Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted

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
Highlighted

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,
Highlighted
Partner
Partner

Hi Sunny, 

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

 

Highlighted
Partner
Partner

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

Highlighted

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