Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

rido1421
Contributor II

Set Analysis

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,

1 Solution

Accepted Solutions
MVP
MVP

Re: Set Analysis

Try like:

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

9 Replies

Re: Set Analysis

Ignore all selections except some specific fields using Set Analysis

or

=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)

MVP
MVP

Re: Set Analysis

Try like:

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

neetha_p
Honored Contributor

Re: Set Analysis

Try:

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

sasiparupudi1
Honored Contributor III

Re: Set Analysis

ex

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

hth

Sasi

rajkumarb
Contributor II

Re: Set Analysis

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

bwisenosimenkg
Valued Contributor

Re: Set Analysis

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

rido1421
Contributor II

Re: Set Analysis

Thank You, Much simpler

bwisenosimenkg
Valued Contributor

Re: Set Analysis

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.

MVP
MVP

Re: Set Analysis

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.

Community Browser