topic Re: If statements in Set Analysis in QlikView

If statements in Set Analysis

Fri, 29 Mar 2013 14:18:38 GMT

I'm using Set Analysis to ensure the x-axis in a bar chart ignores the Year filter (so I can show YOY comparisons).

Here is the formula I'm using: SUM ({<YEAR=>} [CUSTOMER BALANCE]) / 1,000,000

Since the most recent data included is as of Feb 2013, whenever a later month is selected the latest year shown is 2012. However I'd like the last bar to still show 2013 with the most recent data of that year available.

So let's say the month of December is selected. I would like all previous years (2008 - 2012) to show December information, but I would like 2013 to still show on the chart using February's data. Is this possible?

Thanks

Re: If statements in Set Analysis

Jason_Michaelides — Fri, 29 Mar 2013 14:23:59 GMT

Can you post a sample of your app?

Re: If statements in Set Analysis

Fri, 29 Mar 2013 14:41:18 GMT

Let's see if this works.

Re: If statements in Set Analysis

Jason_Michaelides — Fri, 29 Mar 2013 16:00:56 GMT

Hi mate,

This may not be the most elegant solution but it works.

1. You need to convert your MONTH to a real month (i.e. numeric underlying value). Do this in your script using

Month(Date#(MONTH,'MMMM'))

MONTH

2. Add a globally calculated variable to get the last month with data of the last year:

vMaxMonthWithBalance: =MaxString({1<YEAR={"$(=Max({<YEAR=,MONTH=>}YEAR))"}>} MONTH)

3. Use an IF statement in the chart.

IF(

(SUM ({<YEAR=>} [CUSTOMER BALANCE])/1000000)=0

,(SUM ({<YEAR=,MONTH={$(vMaxMonthWithBalance)}>} [CUSTOMER BALANCE])/1000000)

,(SUM ({<YEAR=>} [CUSTOMER BALANCE])/1000000)

)

See attached.

Hope this helps,

Jason

Re: If statements in Set Analysis

Mon, 01 Apr 2013 18:10:49 GMT

Who needs elegance? Not only did this work - but it taught me a few other things about Qlikview that I didn't previously know (using both Load and SQL Select in the script, adding variables, etc.).

I really appreciate the help Jason!

Cheers.