7 Replies Latest reply on May 2, 2018 6:10 PM by bala kumar

# Data variance data based on different measures

Hi All,

I need to do the data variance calculation for different measure. The date variance calculation is between max date & maxdate -7days and I need the output as below.

I am attaching the TWBX file.

It would be highly appreciated if someone could help me this output.

Thanks,

Bala

• ###### 1. Re: Data variance data based on different measures

Hi Bala,

The easiest way to do this is to treat each measure separately (in a separate worksheet) - that way we can add the a calculated field based on the measure e.g for your rates calc I have created a calulated field "rate direction":

IF ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1) > 0 THEN "UP"

ELSEIF ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1) < 0 THEN "Down"

End

We can then add to the shapes shelf (and to the color shelf) and then choose the appropriate shape (and color). Then the worksheets can be brought together in a dashboard.

In the attached I have done this for rates, but the same can be applied to your other measures.

• ###### 2. Re: Data variance data based on different measures

Hi Paul,

In my case, How can i get the difference of the values as when I do the table calculation it doesn't provide me the same output format.

Even If I calculate the difference in a separate sheet and i am not able to align the same in dashboard.

Could you help on getting the difference & diff %

Thanks,

Bala

• ###### 3. Re: Data variance data based on different measures

Sure...here's the step by step process.

Step 1: Simplify worksheet to a single measure:

Step 2: Change measure to a difference (and/or % difference)

Step 3: Drag rate back onto text shelf

Step 4: Double click on the Table calc and copy the formula which is: ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1)

Step 5: Create a new calculated field “Rate direction” which uses this formula:

IF ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1) > 0 THEN "UP"

ELSEIF ZN(SUM([Rate])) - LOOKUP(ZN(SUM([Rate])), -1) < 0 THEN "Down"

End

Step 6: Drag “Rate Direction” onto the shape shelf. Ensure the table calc is set to compute as “table across” and select the appropriate shape for up/down/steady.

Step 7: Drag “Rate direction” onto colour shelf and select colours for up/down/steady (choose white for steady so that it is not displayed against white background)

Step 8 (optionally) remove the Table calc from the text shelf so the difference value isn’t displayed.

Then if you create 3 sheets, one for each measure, you should be able to align in your dashboard no problem - we know they will all be 1 row and 2 columns, so shouldn't be an issue - unless I'm misunderstanding something?

• ###### 4. Re: Data variance data based on different measures

Thanks a lot Paul. Highly appreciated.

Will try the solution and update you on it.

Thanks,

Bala

• ###### 5. Re: Data variance data based on different measures

Paul, In this case i need the difference value and the up/down symbol as a third column as like how we get in excel cell by cell difference.

Regards,

Bala

• ###### 6. Re: Data variance data based on different measures

We can do that again by splitting into different sheets. See attached. However, I would generally recommend on going the path of trying to replicate an excel file. It's often possible, but can add complexity and difficulty and there may just be a different way of looking at the data which is much simpler (and performs better) and gives the same answer, just in a slightly different way.

• ###### 7. Re: Data variance data based on different measures

Thanks Paul. It all worked well.