topic Re: Set Analysis in QlikView

Set Analysis

rido1421 — Mon, 21 Sep 2015 08:17:24 GMT

Hi There

I would like to write a set analysis expression which does the following.

Sum(Finance_Amount)- and ignore any selections made except The Year and the Month selections.

I have managed to do it using a few variables but I would like to see if there is a simpler way of doing it?

Regards,

Re: Set Analysis

MK_QSL — Mon, 21 Sep 2015 08:25:26 GMT

Ignore all selections except some specific fields using Set Analysis

=Sum({1<

Year = {$(=If(GetSelectedCount(Year) = 0,Chr(39) & '*' & Chr(39),Concat (Distinct Chr(39) & Year & Chr(39),',')))},

Month = {$(=If(GetSelectedCount(Month) = 0,Chr(39) & '*' & Chr(39),Concat (Distinct Chr(39) & Month & Chr(39),',')))}

>}Finance_Amount)

Re: Set Analysis

tresB — Mon, 21 Sep 2015 08:26:30 GMT

Try like:

Sum({1<Year=p(Year), Month=p(Month)>}Finance_Amount)

Re: Set Analysis

Anonymous — Mon, 21 Sep 2015 08:27:37 GMT

Try:

Sum({<Year = Max(Year),Month = Max(Month) Region =,Product=>}Finance_Amount)

Re: Set Analysis

sasiparupudi1 — Mon, 21 Sep 2015 08:28:45 GMT

Sum({<Year={"=$(Max(Year))"},Month={"=$(Max(Month))"}, Dim1=,Dim2,Dim3>}Finance_Amount)

hth

Sasi

Re: Set Analysis

rajkumarb — Mon, 21 Sep 2015 08:29:30 GMT

HI Rido

may be this

Sum({1<[Year]={'2015'}, [Month]={'April'}>}Finance_Amount)

choose your Month & Year

Or use Max(Year), Max(Month) for Current Month & Year

Re: Set Analysis

simenkg — Mon, 21 Sep 2015 08:45:13 GMT

There is a syntax for this, and it is as follows:

Sum({1<[Year]=$::[Year], [Month]=$::[Month]>} Finance_Amount)

The 1 says that the expression should take the values from the unfiltered set, so it disregards all selections.

The [Set]:: syntax says that the expression should take the values from the specified Set for that field. $ is the current selections Set.

Hope this works for you

Regards
SKG

Re: Set Analysis

rido1421 — Mon, 21 Sep 2015 08:46:24 GMT

Thank You, Much simpler

Re: Set Analysis

simenkg — Mon, 21 Sep 2015 08:50:39 GMT

This will not work, because the p(Year) can be influenced by other selections.

Say if a company has only sold a product in 2014, then selecting that product will reduce p(Year) to only 2014.

You should use the [Year]=$::[Year] syntax as it will only use the explicit selection for Year.

Re: Set Analysis

tresB — Mon, 21 Sep 2015 09:24:00 GMT

Thanks bwisenoSimenKG‌, you have a valid point. I had an impression that :: is meant for Alternate State and $:: is used for default state, didn't notice it works for Current Selection as well. Thank you once again. I was trying to play with this approach to check if by chance we are mixing it up, but not so far. It seems yours to be right.