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: 
geertdeman
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 (1)
2 Solutions

Accepted Solutions
sunny_talwar

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

sunny_talwar

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
sunny_talwar

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))
geertdeman
Contributor III
Contributor III
Author

Thanks!!! it is working
geertdeman
Contributor III
Contributor III
Author

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

sunny_talwar

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

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.

sunny_talwar

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)
geertdeman
Contributor III
Contributor III
Author

also that is working!   

Thanks. 

sunny_talwar

Awesome 🙂
geertdeman
Contributor III
Contributor III
Author

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