# QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for
Did you mean:
Highlighted
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)

• ### QLIKVIEW Chart

1 Solution

Accepted Solutions
Highlighted
MVP

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))``````
4 Replies
Highlighted
MVP

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

Hi Sunny,

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

Highlighted
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
MVP

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