topic Re: If statement within set analysis in QlikView

If statement within set analysis

zagzebski — Mon, 25 Aug 2014 12:20:02 GMT

I am trying to find the most efficient way to address two flags that we use... (1) a flag that is for Year to Date amount and (2) a flag that is for the last 12 months.

I want to incorporate the best way to address the flags (above) when a user clicks the "YTD" button or the "TMT" (twelve months trailing) button.

So what is the most efficient way to choose one of these expressions based on the YTD or TMT selection. Is there way to have an If statment WITHIN the set analysis of one expression?

I have about 75 charts I have to address this with so looking for the most efficient way! The stripped down version of the expressions I need based on user selection:

sum({<YTD={'1'}>}Amount)

and

sum({<TMT={'1'}>}Amount)

THanks in advance

Re: If statement within set analysis

tresB — Mon, 25 Aug 2014 12:27:21 GMT

It should be IF outside set analysis and a simple one like:

=If( <TMT condition>, sum({<TMT={'1'}>}Amount) , sum({<YTD={'1'}>}Amount) )

Or, you might want to put another condition in else part like:

If( <TMT condition>, sum({<TMT={'1'}>}Amount) , If( <YTD Condition>, sum({<YTD={'1'}>}Amount) ))

Re: If statement within set analysis

Anonymous — Mon, 25 Aug 2014 12:29:56 GMT

How about :

Creating a couple of variables
Having your buttons set the variable values
- As in YTD button sets it to sum({<YTD={'1'}>}Amount
Use a dollar expansion of the variable in your expression

Re: If statement within set analysis

Anonymous — Mon, 25 Aug 2014 12:39:27 GMT

if the flags are always contrary (means YTD = 1 while TMT = 0 and vice versa) you do not need an if statement.

are the flags built in your calendar and connected to the facttable?

why

sum(Amount)

is not enough? pressing the button should select YTD or TMT and the correct data should be selected. I would build my datamodel to do so

Re: If statement within set analysis

zagzebski — Mon, 25 Aug 2014 12:54:20 GMT

Well my expressions have a little more in that each chart accounts for 2 previous time frames so:

Expression 1 : YTD this year or Last 12 months (depending on YTD or TMT selection)

Expression 2: YTD last year or prior 13-24 months (depending on YTD or TMT selection)

Expression 3: YTD 2 years prior or prior 25-36 months (depending on YTD or TMT selection)

So I have additional criteria in the set that includes the max, max-1, max-2 for year in the expressions, but I do not want that part of the set when TMT is selected as it will cross multiple years (ie August 2013-July 2014 when TMT is selected)

Make sense?

Re: If statement within set analysis

zagzebski — Mon, 25 Aug 2014 13:00:55 GMT

I really like where this is headed but it is new to me so a question:

Would the expression have just one variable in it? I know in theory what you are saying but struggling to figure out what the expression would look like and how it would change depending on button selection.

Re: If statement within set analysis

Anonymous — Mon, 25 Aug 2014 13:05:26 GMT

I have a similiar application where I show the YTD or TMT data of the year and the previous year

my expression is

sum ({>...

Kalenderdates={'>=$(=vDateFrom)<=$(=vDateTo)'},

....>}

)FactValue

The variables vDateFrom and vDateTo are set (depending on YTD or TMT)

e.g. 1.1.2014 to 30.06.2014 YTD or 1.3.2014 to 30.4.2014 TMT

A similiar expression and variables can do it for Prev Year and Year -2

in my application the user switches between YTD and TMT using a button

Result: my expressions in many charts are much easier to read and maintain

Re: If statement within set analysis

zagzebski — Mon, 25 Aug 2014 13:21:03 GMT

I like it. Just kind of new to me so thinking about what the specifics would be. So you click on YTD and it populates the variable with one expression, then if they click on the TMT button it populates the variable with a different expression? Then the variable(s) is utilized in the expression?

Re: If statement within set analysis

Anonymous — Mon, 25 Aug 2014 13:28:29 GMT

yes, that's it. my variable

vDateFrom = if YTD,yearStart(SelDate),monthstart(SelDate) (shows principal)

vDateTo = monthend(SelDate)

if minimum date to report is month,

User selects July!

YTD: vDateFrom: 1.1.2014; vDateTo 31.7.2014

TMT vDateFrom: 1.7.2014; vDateTo: 31.7.2014

if you select more than 1 month (only for TMT) then the variables correspond to the months

hope that gives you an idea

Re: If statement within set analysis

zagzebski — Mon, 25 Aug 2014 13:29:28 GMT

And the additional twist (sorry) is that if neither YTD or TMT buttons are selected the expression should just respected whatever calendar dates are selected.

Re: If statement within set analysis

Anonymous — Mon, 25 Aug 2014 13:37:22 GMT

in my case there is YTD or TMT selected, if in your case neither of them needs to be selected, so what calendar should be used? you must provide a third scenario for that

Re: If statement within set analysis

zagzebski — Mon, 25 Aug 2014 13:44:02 GMT

Thanks! I am going to run with this.