Background colour formatting based on last 3 months average values

[#1]

Hi

I am a newbie to qlikview and would really appreciate if the community folks can help me out on this task given to me.

Below is a snap shot of the design which is expected.

     1. Report should show all 12 months numbers along with the monthly YTD numbers.

     2. The BG colour coding should apply to all months and KPIs.

     3. Colour coding  for Month column needs to be based on comparing current month with average of last 3 months

     4. Colour coding for YTD is by checking whether value is within a range or not.

I have also attached a dummy set of data for reference.

Really appreciate help!!!

thanks,

Amol

[#2]

post your sample application to understand the logic

[#3]

Hi Bhawna,

I have attached a sample. I need average value for last 3 months in column 'Last 3 months'.

I am not sure how it can be achieved since the data is getting split by months.

In the final design, the colour formatting will be based on comparing the actuals with the average of last 3 months.

Thanks,

Amol

[Anonymous · Accepted solution]

Hello,

I attached a qvw with your data and a pivot with a setting in background colour of the expression.

I didn't know the colours you need, so I chose green and red for the comparisono between current month and the average of the previous 3 month.

Obviously, if you data begin from Jan-2013, for the first 3 months there will not be the comparison with the average, so the colour may not be correct.

Let me know!

BR,

Elena

[#5]

Hi Elena,

Great thanks Elena!!!

I also need to show the same logic for Jan Feb and March.

Can I input a user defined variable for year input and based on that I can filter the data for current selected and previous year(Oct/Nov/Dec).

Thanks,

Amol

[#6]

Hi Elena,

I have found a way. Please share your thoughts.

if( (before(sum({<Year=>}Value),1)+ before(sum({<Year=>}Value),2)+before(sum({<Year=>}Value),3))/3 < sum(Value), Green(), lightred())

Thanks,

Amol

[Anonymous]

Hi

I'm not understanding you requirement...

For the first 3 months you can't perform a comparison because you don't have any values (in this case you don't have Oct, Nov,Dic 2012)

Your set analysis Year=  just disregards the selection on year, and considers all years you have.

Elena

[#8]

Hi Elena,

The actual source will include data from Jan 2011 till Dec 2014.

so for all the years except 2011, we will compare Jan with average of Oct/Nov/Dec from previous year.

Similarly, for Feb with Nov/Dec/Jan and so on..

For year 2011, we will not have any comparative analysis for Jan/Feb/March.

Hope I have clarified your doubt.

Amol