Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
l_smythe80
Partner - Contributor II
Partner - Contributor II

First sorted value count by year

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

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

4 Replies
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,
l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

Hi Sunny, 

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

 

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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

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