compare months

[surajap123]

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.

[vishsaggi]

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

[surajap123]

Thanks.

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

[vishsaggi]

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

[vishsaggi]

Try this in your background expression:

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

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

[surajap123]

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)

!

[vishsaggi]

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.

[kamielrajaram]

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