Answered

Using Distinct with number of entries to show 10 results

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi there.

I've built an app for my company to track service calls in order to register the number of errors in the past.

We have now decided to build further upon this and include an information page showing the top 10 frequent clients the last 3 months.

But i'm having some problems:

Right now i have a Gallery set up showing the first 10 clients and a label showing the amount of times ThisItem.Result comes up.

I've attached screenshots showing the layout and code - but to make it easier, I'll add them here too.

Gallery: FirstN(Sort(Distinct(TicketsCollect;Accountname);Result);10)

Ticket count: CountIf(TicketsCollect;ThisItem.Result = Accountname)

Client name in Gallery: Result

So here's what I'm actually trying to do:

I want to list the top 10 clients based on the amount of tickets they have had over the last 3 months (changing as time goes by) in descending order.

I've been trying to read up on different functions but I am having a hard time incorporating this to practical use.

Any tips or help is much appreciated.

- Anders

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,
You might also try to get you mind around as I have commented this as best I can. I have done a test at this end on a similar data structure and got the desired results in a gallery.
With( { vTickets: //set a temporary variable Filter( TicketsCollect; //on your data source DateDiff( //date difference YourDateFieldName; //from your date field Today(); //to today Days //in days ) < 90 //is less than 90 ) }; FirstN( //get the first number of records Sort( //and sort as below AddColumns( //add the count column GroupBy( //group by required field vTickets; //the data set from variable above "Accountname"; //field mentioned above "OtherData" //rest of fields go in this table record ); "CountNo"; //call the count this name CountRows(OtherData) //set count as total rows in OtherData ); CountNo; //sort on count field Descending //biggest on top ); 10 //and get the first 10 ) )
This should work for you as the Items of a Gallery. Your gallery fields will be Accountname and CountNo

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous ,
Note I have updated a few things since my original post. I have tested this and it works.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @WarrenBelz

Thank you for your help.
It seems to be working 🙂

If i would like to make it so entries older than 90 days arent shown if there isnt 10 new - how would I do this?

Right now, even if I change it to 1 day - it still shows 10 entries.

The idea is to only show new entries - even if there isn't 10 new in the timeperiod.

Was this reply helpful? Yes No
WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

HI @Anonymous ,
Sorry - date reference wrong way around - typo in translating to your formula - I have fixed it in the original post.
Should be date field first and then Today() to make it a positive result.
It should work for you now.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ，

Do you mean that no matter whether time is within 90 days, you always want 10 records?

If so, you just need to not use the first part formula that he posted @WarrenBelz , and make a little change on the second part.

set the gallery's Items:

FirstN( SortByColumns( AddColumns( GroupBy( TicketsCollect; "Accountname"; "OtherData" ); "CountNo"; CountRows(OtherData); "latestdate"; First(Sort(TicketsCollect;YourDateFieldName)).YourDateFieldName ); //get the latestdate "latestdate"; //firstly sort based on date "CountNo"; //then sort based on countno Descending ); 10 )

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz thank you very much for your help.

I have used far too much time trying to figure out how to do this.

Was this reply helpful? Yes No
WarrenBelz 156,425 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous no problems - I actually took that on as a bit of as challenge for myself as it combined quite a number of elements.
@v-yutliu-msft I have tested your formula on the same data set as mine and it is valid, but produces no records.

Was this reply helpful? Yes No