topic Re: compare months in QlikView

compare months

surajap123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I want to compare between months. I have pivot table and the there are 3 dimensions in chart and 2 horizontal dimension(along with month).

So in a new column, i want to show GREEN color when current month is more than previous, otherwise show red color.

Please help.

Re: compare months

vishsaggi — Fri, 15 Sep 2017 15:59:48 GMT

check this?

In expression tab click on + sign and in Background expression write like:

= IF(sum(Sales)/sum(Target) > Before(sum(Sales)/sum(Target)), LightGreen(), LightRed())

OR using the expression Label name like:

= IF(sales1 > Before(sales1), LightGreen(), LightRed())

Re: compare months

surajap123 — Fri, 15 Sep 2017 16:42:51 GMT

Thanks.

The first month is showing red, even though there is no prior month.

Re: compare months

vishsaggi — Fri, 15 Sep 2017 16:49:04 GMT

What do you want to display for that? Like no color or any other color?

Re: compare months

vishsaggi — Fri, 15 Sep 2017 16:54:50 GMT

Try this in your background expression:

= IF(ISNULL(Before(Sales1)), White(),

IF(Sales1 > Before(Sales1), LightGreen(), LightRed()))

Re: compare months

surajap123 — Fri, 15 Sep 2017 17:47:33 GMT

Thanks!

I actually want the output as shown in the screenshot. I am not sure, if it is possible using pivot table.

Between every month there is a comparison column that should show the color(red for low sales compared to previous month, green for increase in sales and yellow for same sales as previous month).

Notice that there are 2 horizontal dimensions. The green count column should show count of green color(ie. positive trend count)

Re: compare months

vishsaggi — Fri, 15 Sep 2017 19:04:57 GMT

Ok. Probably we need to use Dimensionality() or Pick(Dim()) Synthetic Dimensions. I will have to try for that. However, will ask stalwar1 if he has any ideas and chip in some time to help us on this. I have worked until this. Check attached.

Re: compare months

kamielrajaram — Fri, 15 Sep 2017 22:17:18 GMT

Good day,

Hope this helps.

Add in another expression and add a space in the definition. Under background color for the new expression add the following.

= if(isnull(Before(sum(Sales))),Yellow(),

if((sum(Sales)/sum(Target))>Before(sum(Sales)/sum(Target)),LightGreen() ,

if((sum(Sales)/sum(Target))< Before(sum(Sales)/sum(Target)),LightRed())))