topic How Can I do this set analisis? in QlikView

How Can I do this set analisis?

rcandeo — Fri, 31 Jul 2009 03:31:52 GMT

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)

How Can I do this set analisis?

Fri, 31 Jul 2009 03:41:58 GMT

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.

How Can I do this set analisis?

Fri, 31 Jul 2009 04:24:46 GMT

NMiller's suggestion should work. Use

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

to get the previous year, as you mentioned.

How Can I do this set analisis?

rcandeo — Fri, 31 Jul 2009 20:21:51 GMT

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.

How Can I do this set analisis?

Fri, 31 Jul 2009 20:55:40 GMT

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

How Can I do this set analisis?

Fri, 31 Jul 2009 21:02:06 GMT

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.

How Can I do this set analisis?

rcandeo — Mon, 03 Aug 2009 23:33:59 GMT

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.

Re: How Can I do this set analisis?

Wed, 04 Jul 2012 23:57:59 GMT

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?