Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
rcandeo
Creator III
Creator III

How Can I do this set analisis?

I have a table with Month, Year and Amount of stocks. I want to show only the amount of the last year and month selected. Ag: If user selects all months of 2008, I want to show only the amount of december.

If I fix the month like above, it works. But How can I make this calculation selecting the last month/year?

sum({$<Month={12}, Year={2008}>} Amount)

1 Solution

Accepted Solutions
Not applicable

Will this work?

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


I'm thinking the single-quotes are unnecessary, but it may depend on your data types.

View solution in original post

7 Replies
Not applicable

Will this work?

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


I'm thinking the single-quotes are unnecessary, but it may depend on your data types.

Not applicable

NMiller's suggestion should work. Use

...Year={$(=Max(Year)-1)}....

to get the previous year, as you mentioned.

rcandeo
Creator III
Creator III
Author

using the expression sum({$<Month={'$(=Max(Month))'}, Year={'$(=Max(Year))'}>} Amount), didn't work cause I need to respect the selection.

If is selected Month april, may and june, I need to get june that is the Maximum month selected, but the expression got december month that is the max month of the table.

Not applicable

To have set analysis disregard any current selections you can substitute the leading $ for a 1, like this


Sum( {1<Month... instead of Sum( {$<Month.....


This method disregards ANY selections you have made. Alternatively, if you are wanting to disregard only the selection on month, but still allow the aggregation set to be narrowed by other selections you can do something like this:


Sum( {$<Month={$(=Max( {1} Month) )}, Year={$(=Max(Year)-1)}>} Amount)


You can play around with the set modifiers to get what your looking for. If you only want the max over a selection, then use the $. If you want the Max regardless of the selection, use the 1. Hopefully this helps.

sjprows

Not applicable

That string will respect the selection. If nothing is selected, the maximum month will be used. If anything is selected, then the maximum selected month will be used.

Take a look at this example. In my example, the months are numbers, so no single quotes are necessary.

rcandeo
Creator III
Creator III
Author

The problem was that month field was not numeric, and in some months (I don't know the cause) were not working. Changing to numeric was the solution. Thank You all.

Not applicable

Hi NMiller

Using your example, what if you want to list the calendar Quarters (report dimension) but consider in the stock evaluation expression only the last month of eack Quarter?