topic Re: Background colour formatting based on last 3 months average values in QlikView

Background colour formatting based on last 3 months average values

Fri, 30 Jan 2015 07:21:08 GMT

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

Re: Background colour formatting based on last 3 months average values

Fri, 30 Jan 2015 07:50:45 GMT

post your sample application to understand the logic

Re: Background colour formatting based on last 3 months average values

Sat, 31 Jan 2015 11:47:54 GMT

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

Re: Background colour formatting based on last 3 months average values

Anonymous — Sun, 01 Feb 2015 14:52:14 GMT

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

Re: Background colour formatting based on last 3 months average values

Mon, 02 Feb 2015 10:31:25 GMT

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

Re: Background colour formatting based on last 3 months average values

Mon, 02 Feb 2015 11:38:18 GMT

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

Re: Background colour formatting based on last 3 months average values

Anonymous — Mon, 02 Feb 2015 11:50:42 GMT

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

Re: Background colour formatting based on last 3 months average values

Mon, 02 Feb 2015 12:09:31 GMT

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