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: 
Not applicable

Set analysis

Hello,

this is my data ,, by default i need the value in textbox to be for last year,last quarter and last month ,,

i tried with the following code =sum({<Year={$(=max(Year))},Qtr={$(=max(Qtr))},Month={$(=max(Month))}>}StkAmt)

but it shows  value as 0 instead of 4 ,,, but when i select something then i get right value ,, can someone help me with script and explain why my script didnt work

4 Replies
Not applicable
Author

Year Month Sum (StkAmt)


86
1 1 6
1 2 7
1 3 5
1 4 4
1 5 7
1 6 8
1 7 3
1 8 2
1 9 7
1 10 5
1 11 8
1 12 2
2 1 6
2 2 7
2 3 5
2 4 4

Hello,

this is my data ,, by default i need the value in textbox to be for last year,last quarter and last month ,,

i tried with the following code =sum({<Year={$(=max(Year))},Qtr={$(=max(Qtr))},Month={$(=max(Month))}>}StkAmt)

but it shows  value as 0 instead of 4 ,,, but when i select something then i get right value ,, can someone help me with script and explain why my script didnt work

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Check if your formula works without the Month condition. If it does, than I believe the problem is in the different Data type. If your Month is formatted as a dual field, then comparing it to max(Month) won't work, because the result of the function max() is a number.

I'd recommend defining a YearMonth field as the MonthStart() date and comparing it to the date(max(YearMonth)) - this way, you replace multiple conditions with a single one, and it works properly.

I'm working on a Set Analysis training class, where I will be covering this and many other tricks related to Set Analysis - stay tuned.

Anonymous
Not applicable
Author

A bit tricky and ugly set analysis but this should pick the Max Year and the Max Month available for that year and sum up the StkAmt for that combo:

=sum({$<Year={$(=Max(Year))}, Month={$(=Max({$<Year={$(=Max(Year))}>} Month))}>} StkAmt)

Not applicable
Author

yes that worked ... thanks