If statements in Set Analysis

[#1]

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

[Jason_Michaelides]

Can you post a sample of your app?

[#3]

Let's see if this works.

[Jason_Michaelides · Accepted solution]

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

AS

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

[#5]

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.