topic First sorted value count by year in QlikView

First sorted value count by year

LeeSmithBtn — Sat, 16 Nov 2024 19:36:38 GMT

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

Re: First sorted value count by year

sunny_talwar — Fri, 22 Nov 2019 12:29:33 GMT

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,

Re: First sorted value count by year

LeeSmithBtn — Fri, 22 Nov 2019 13:05:01 GMT

Hi Sunny,

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

Re: First sorted value count by year

LeeSmithBtn — Fri, 22 Nov 2019 13:07:57 GMT

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

Re: First sorted value count by year

sunny_talwar — Fri, 22 Nov 2019 14:17:04 GMT

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))