Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Sum value for max date

I need the total  value for each company for the maximum month (and also for maximum month -1).  My calculation is working as long there is data for all months but once there is a month missing (in the example november is missing for company A)  the calculation of that value is wrong.  This is the calculation I used for Sum Cur Month: sum({<YearMonth={"$(=Date(max(Date#(YearMonth, 'MMM-YYYY')),'MMM-YYYY'))"}>}
Counter). 

The calculation that I used for column current month is working like expected: Date(max(Date#(YearMonth, 'MMM-YYYY')),'MMM-YYYY')

Why is that sum function not using the maximum month value for that company as filter, it seems like it is using november 2018 for all companies

 

 

sum example.jpg(Photo shows a simplified version of the data, it is also possible to filter on company, period and a couple of other dimensions.  Data should be updated with the selected filters)

Labels (3)
2 Solutions

Accepted Solutions
Highlighted

Re: Sum value for max date

Set analysis is evaluated once per chart and not once per dimension... in your case, set analysis is evaluated to be Nov-2018 for the whole chart... so unfortunately, you cannot use set analysis in this case, unless you create a max month flag in the script and use that in your set analysis where for company A, Oct is flagged as 1 and for Company B and C, Nov is flagged as 1.

Alternative to this would be to use something like this

FirstSortedValue(Aggr(Sum(Counter), YearMonth, Company), -Aggr(Date#(YearMonth, 'MMM-YYYY'), YearMonth, Company))

View solution in original post

Highlighted

Re: Sum value for max date

Then I believe dollar sign expansion should work

$(= '[' & Pick($(vAux.Compliance.QPPVFlagValue),
'REC_COMP_CATEGORY_CHANGED',
'REC_COMP_REGION_GROUPING',
) & ']')

View solution in original post

13 Replies
Highlighted

Re: Sum value for max date

Set analysis is evaluated once per chart and not once per dimension... in your case, set analysis is evaluated to be Nov-2018 for the whole chart... so unfortunately, you cannot use set analysis in this case, unless you create a max month flag in the script and use that in your set analysis where for company A, Oct is flagged as 1 and for Company B and C, Nov is flagged as 1.

Alternative to this would be to use something like this

FirstSortedValue(Aggr(Sum(Counter), YearMonth, Company), -Aggr(Date#(YearMonth, 'MMM-YYYY'), YearMonth, Company))

View solution in original post

Highlighted
Contributor III
Contributor III

Re: Sum value for max date

Thanks!!! it is working
Highlighted
Contributor III
Contributor III

Re: Sum value for max date

that is working for the current month, but can I use that also for the previous month?  In the output I show the value for the current month and in another column the value of the previous month

Highlighted

Re: Sum value for max date

Would you be able to elaborate on what you would want to see as the output based on your sample data?
Highlighted
Contributor III
Contributor III

Re: Sum value for max date

It is the data for the previous month, it is visible in the example, the column Sum prev.  For company B and C it is the value for October, for A it is the data for September.  Additional question, what if there are some default filters that needs be applied on other fields, not visible in the app.

Highlighted

Re: Sum value for max date

You only have Company as the dimension of your chart? If that is correct, try this

FirstSortedValue(
Aggr(
Sum(Counter)
, YearMonth, Company),
-Aggr(
Date#(YearMonth, 'MMM-YYYY')
, YearMonth, Company),
2)
Highlighted
Contributor III
Contributor III

Re: Sum value for max date

also that is working!   

Thanks. 

Highlighted

Re: Sum value for max date

Awesome 🙂
Highlighted
Contributor III
Contributor III

Re: Sum value for max date

Another issue that I have, that first column company is a calculation: 

Pick($(vAux.Compliance.QPPVFlagValue),
[REC_COMP_CATEGORY_CHANGED],
[REC_COMP_REGION_GROUPING],
)

But that doesn't seems to work as field value in the firstsortedvalue function