11 Replies Latest reply on Sep 11, 2019 12:57 PM by Utkarsh Agrawal

'Difference' Table Calc query

Utkarsh Agrawal Sep 11, 2019 10:42 AM

Hi,

I am working on a calc to show me difference of a certain measure for [Month]2019 - [Month]2018. It is working as expected, but when I do this in table for 2 or more year, the oldest year would have figures not as expected.

Below is the picture of when all the years are selected in filter. Notice the figures in year 2018.

Below is the snap of when 2017 is unchecked.

The figures here change. I am trying to understand what is the reason behind it also what are the figures in 2017 when the data for 2016 doesn't exist.

You can check the table calc in the attached workbook to see what exactly I am doing. Please let me know if I need to elaborate.

Thanks

Utkarsh

DifferenceTableCalc.twbx 23.7 KB

1. Re: 'Difference' Table Calc query

Ken Flerlage Sep 11, 2019 10:49 AM (in response to Utkarsh Agrawal)

Other than the first row of each year, the numbers still look the same. What am I missing?
Like Show 0 Likes(0)

Actions
2. Re: 'Difference' Table Calc query

Ken Flerlage Sep 11, 2019 10:51 AM (in response to Ken Flerlage)

Actually, looks like C is wrong. Can you be sure that you set that table calculation to compute using "Table Down"?
Like Show 0 Likes(0)

Actions
3. Re: 'Difference' Table Calc query

Bryce Larsen Sep 11, 2019 12:00 PM (in response to Utkarsh Agrawal)

One possible solution if you want to keep January is to add a flag such as FIRST() after the Year dimension and ensure it's only using Year (so 2017 in the example above is 0). Right click on the 0 in this new field and hit Hide.

This should result in hiding the first year worth of data but retain all the values.
Then unclick Show Header for the FIRST dimension in the row pane. Result:

But agree with Ken Flerlage - believe C is using a different table calc than the others.
Like Show 0 Likes(0)

Actions
4. Re: 'Difference' Table Calc query

Utkarsh Agrawal Sep 11, 2019 11:57 AM (in response to Ken Flerlage)

Hey Ken,

That was embarrassingly easy. Thank you so much!
Like Show 1 Likes(1)

Actions
5. Re: 'Difference' Table Calc query

Utkarsh Agrawal Sep 11, 2019 11:59 AM (in response to Bryce Larsen)

Hey Bryce,

That is a great solution as well. Thank you, I could use this somewhere else!
Like Show 1 Likes(1)

Actions
6. Re: 'Difference' Table Calc query

Ken Flerlage Sep 11, 2019 12:06 PM (in response to Utkarsh Agrawal)

No problem. Sometimes you just need another set of eyes! I've been there many times.
Like Show 0 Likes(0)

Actions
7. Re: 'Difference' Table Calc query

Utkarsh Agrawal Sep 11, 2019 12:40 PM (in response to Ken Flerlage)
Hey Ken,

I just noticed that the table calc for 'A' and 'B' weren't accurately set. So it turns out that what I required was there in 'C' and the 'Table Down' doesn't exactly help my need. If you check out my table calc for all the three measures you will see what I mean. So in a way 'Table Down' is not helping and I am back to where I started. Sorry for the confusion.

In this case I wonder Bryce's solution would work. Bryce Larsen could you please post the workbook to refer? Because I created the FIRST() calc and it is not working like what I see in your screenshot.

DifferenceTableCalc.twbx 23.9 KB
Like Show 0 Likes(0)

Actions
8. Re: 'Difference' Table Calc query

Bryce Larsen Sep 11, 2019 12:45 PM (in response to Utkarsh Agrawal)
Please see the attached:

*Reposted due to forgetting to hide year.

DifferenceTableCalc_v2018.3.twbx 24.9 KB
Like Show 0 Likes(0)

Actions
9. Re: 'Difference' Table Calc query

Utkarsh Agrawal Sep 11, 2019 12:50 PM (in response to Bryce Larsen)

Thanks for introducing me to flags. This is very helpful!
Like Show 1 Likes(1)

Actions
10. Re: 'Difference' Table Calc query

Bryce Larsen Sep 11, 2019 12:52 PM (in response to Utkarsh Agrawal)

They can be really useful. For instance, if you want to color This FY, Last FY, etc., use flags to ensure This FY stays the same color year to year rather than being based on "2019" and so on.

Plus you can use flags and hiding elements to do some tricky calculations without using LOD expressions.

Good luck!
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
11. Re: 'Difference' Table Calc query

Utkarsh Agrawal Sep 11, 2019 12:57 PM (in response to Bryce Larsen)

Wow I believe there are many such use cases where it can be used. Thanks!
Like Show 1 Likes(1)

Actions