First sorted value count by year

[LeeSmithBtn]

Thanks @sunny_talwar 

have updated a seperate thread with regards to the issue i face...

on this complicated aggr function thanks to @sunny_talwar  i would like to incorporate some set analysis to look at dates also...

so i would like the logic to look for differences between the last 12 months and the previous 12 months to show the variation across the 2 12 month periods..

so the same as this but with PurchaseDate >12 months ago as 1 expression to show current 12 months:

Min({<Customer>} total <Customer>
Aggr(If(Only({<Customer>}PricePaid) = FirstSortedValue({<Customer>} TOTAL <Customer> PricePaid, -Aggr(Sum({<Customer>}[Quantity]), Customer, PricePaid)),
Only({<Customer>}PricePaid)), Customer, PricePaid))

and the same logic then showing the previous 12 months before that. as you can see from the application as attached, if i select purchase dates from Dec-18 (dynamic 12 months back from the Max date per customer) to Nov-19.

for customer 3 this shows as 55.561 as most frequent pricepaid by sum quantity from dec-18 through Nov-19

same customer shows 528 as most frequent price paid by sum of quantity from Jan-18 through Nov-18.

so using the above logic to find price paid in last 12 months vs the same figure the 12 months previous

any help would be great thank you and hope this makes sense

[sunny_talwar]

Would you be able to create the MonthYear field like this in the script and reload and reattach it here

Date(MonthStart(TempDate), 'MMM-YYYY') 				as MonthYear,

[LeeSmithBtn]

Hi Sunny, 

It should have a field like this already in the app (unless i have published the wrong app)

[LeeSmithBtn]

My Bad, i see what you mean... here is updated... you should be ok reloading the app as its all in the script, no external files required

thank you as always

[sunny_talwar · Accepted solution]

Sorry about that, I didn't realize it was an Inline Load... try this

Min({<MonthYear = {">=$(=Date(MonthStart(Min(MonthYear), -12), 'MMM-YYYY'))<=$(=Date(MonthStart(Max(MonthYear), -12), 'MMM-YYYY'))"}>} TOTAL <Customer> Aggr(
	If(Only({<Customer, MonthYear = {">=$(=Date(MonthStart(Min(MonthYear), -12), 'MMM-YYYY'))<=$(=Date(MonthStart(Max(MonthYear), -12), 'MMM-YYYY'))"}>} PricePaid) = FirstSortedValue({<Customer, MonthYear = {">=$(=Date(MonthStart(Min(MonthYear), -12), 'MMM-YYYY'))<=$(=Date(MonthStart(Max(MonthYear), -12), 'MMM-YYYY'))"}>} TOTAL <Customer> PricePaid, -Aggr(Sum({<Customer, MonthYear = {">=$(=Date(MonthStart(Min(MonthYear), -12), 'MMM-YYYY'))<=$(=Date(MonthStart(Max(MonthYear), -12), 'MMM-YYYY'))"}>}[Quantity]), Customer, PricePaid)), Only({<Customer, MonthYear = {">=$(=Date(MonthStart(Min(MonthYear), -12), 'MMM-YYYY'))<=$(=Date(MonthStart(Max(MonthYear), -12), 'MMM-YYYY'))"}>}PricePaid))
, Customer, PricePaid))