Taking difference from two tables with different but related calculated dimension

[BrentSmits]

Hey Guys!

This might have been posted before but I can't find it directly so I'm going to ask.

I have a table that used a calculated dimension. A specific variable is used in combination with a field, this results in an integer number and the integer number is than spread amongst some buckets with a bunch of if statements in the calculated dimension like this:

=if(vCalculationDate-[Due Date] <= 0, '1. Current',
if(vCalculationDate-[Due Date] > 0 and vCalculationDate-[Due Date] <= 30, '2. 1-30',
if(vCalculationDate-[Due Date] > 30 and vCalculationDate-[Due Date] <= 60, '3. 31-60',
if(vCalculationDate-[Due Date] > 60 and vCalculationDate-[Due Date] <= 90, '4. 61-90',
if(vCalculationDate-[Due Date] > 90 and vCalculationDate-[Due Date] <= 180, '5. 91-180',
if(vCalculationDate-[Due Date] > 180 and vCalculationDate-[Due Date] <= 270, '6. 181-270',
if(vCalculationDate-[Due Date] > 270 and vCalculationDate-[Due Date] <= 365, '7. 271-365', '8. 365+'
)))))))

The reason I'm not doing it in the script, is because the user needs to be able to choose the vCalculationDate.

Based on this calculated dimension and a very easy expression: sum(Values), I get a pivot table with the total value for each bucket.

Now imagine another table, that is exactly the same, but instead of vCalculationDate, it takes vCalculationDate and subtracts 1 month from it in the calculated dimension. This means that there is a shift in the buckets. Note that my expression is in fact a little bit more complex, it used a set expression and 2 variables. I also take this expression in this table but subtract 1 month from each variable.

Now I would in fact, like to have just 1 table, that calculated the difference of those 2 previously mentioned tables. I'm struggling with this because I have no idea how I can combine these calculated dimensions. If it were just taking the difference but keeping the same calculated dimension, I would just have to use sum(values) - sum(values but with variable that is 1 month lower). 

Is such a thing possible? If it is, how would I have to set this up.

I'm not exactly able to provide an extract of the qvd but if this is required, let me know and I'll see what I can do.

Kind regards and thank you in advance!

[BrentSmits]

This might have been posted before but I can't find it directly so I'm going to ask.

I have a table that used a calculated dimension. A specific variable is used in combination with a field, this results in an integer number and the integer number is than spread amongst some buckets with a bunch of if statements in the calculated dimension like this:

=if(vCalculationDate-[Due Date] <= 0, '1. Current',
if(vCalculationDate-[Due Date] > 0 and vCalculationDate-[Due Date] <= 30, '2. 1-30',
if(vCalculationDate-[Due Date] > 30 and vCalculationDate-[Due Date] <= 60, '3. 31-60',
if(vCalculationDate-[Due Date] > 60 and vCalculationDate-[Due Date] <= 90, '4. 61-90',
if(vCalculationDate-[Due Date] > 90 and vCalculationDate-[Due Date] <= 180, '5. 91-180',
if(vCalculationDate-[Due Date] > 180 and vCalculationDate-[Due Date] <= 270, '6. 181-270',
if(vCalculationDate-[Due Date] > 270 and vCalculationDate-[Due Date] <= 365, '7. 271-365', '8. 365+'
)))))))

The reason I'm not doing it in the script, is because the user needs to be able to choose the vCalculationDate.

Based on this calculated dimension and a very easy expression: sum(Values), I get a pivot table with the total value for each bucket.

Now imagine another table, that is exactly the same, but instead of vCalculationDate, it takes vCalculationDate and subtracts 1 month from it in the calculated dimension. This means that there is a shift in the buckets. Note that my expression is in fact a little bit more complex, it used a set expression and 2 variables. I also take this expression in this table but subtract 1 month from each variable.

Now I would in fact, like to have just 1 table, that calculated the difference of those 2 previously mentioned tables. I'm struggling with this because I have no idea how I can combine these calculated dimensions. If it were just taking the difference but keeping the same calculated dimension, I would just have to use sum(values) - sum(values but with variable that is 1 month lower). 

Is such a thing possible? If it is, how would I have to set this up.

I'm not exactly able to provide an extract of the qvd but if this is required, let me know and I'll see what I can do.

Kind regards and thank you in advance!

[sunny_talwar]

It will help to see what you have in a qvw and play around with it to see if this can be done or not.

[BrentSmits]

I tried to prepare a qvw but unfortunately there are certain fields that won't scramble that are too sensitive to leave in the qvw (phonenumbers). I don't see a way to remove them so I am not able to send you a qvw. 

[sunny_talwar]

Would you be able to mock up some data with which you can recreate this issue and show it to us?

[qliksus]

You want have 2 different set of calculated dimension and each have 2 different set of calculation  . You want to show this in one table  and  the corresponding calculation ,  if that is what you want you can just add dummy dimension  which has 2  values lets say  1  and 2  ,  now  based on this field you differentatte ur dimension and calculation 

Dimension: 

=  if ( Dummydim = 1 ,

  if(vCalculationDate-[Due Date] <= 0, '1. Current',
if(vCalculationDate-[Due Date] > 0 and vCalculationDate-[Due Date] <= 30, '2. 1-30',
if(vCalculationDate-[Due Date] > 30 and vCalculationDate-[Due Date] <= 60, '3. 31-60',
if(vCalculationDate-[Due Date] > 60 and vCalculationDate-[Due Date] <= 90, '4. 61-90',
if(vCalculationDate-[Due Date] > 90 and vCalculationDate-[Due Date] <= 180, '5. 91-180',
if(vCalculationDate-[Due Date] > 180 and vCalculationDate-[Due Date] <= 270, '6. 181-270',
if(vCalculationDate-[Due Date] > 270 and vCalculationDate-[Due Date] <= 365, '7. 271-365', '8. 365+'
)))))))  ,

  if(vCalculationDate-1month-[Due Date] <= 0, '1. Current',
if(vCalculationDate-1month-[Due Date] > 0 and vCalculationDate-[Due Date] <= 30, '2. 1-30',
if(vCalculationDate-1month-[Due Date] > 30 and vCalculationDate-[Due Date] <= 60, '3. 31-60',
if(vCalculationDate-[Due Date] > 60 and vCalculationDate-[Due Date] <= 90, '4. 61-90',
if(vCalculationDate-1month-[Due Date] > 90 and vCalculationDate-[Due Date] <= 180, '5. 91-180',
if(vCalculationDate-1month-[Due Date] > 180 and vCalculationDate-[Due Date] <= 270, '6. 181-270',
if(vCalculationDate-1month-[Due Date] > 270 and vCalculationDate-[Due Date] <= 365, '7. 271-365', '8. 365+'
)))))))

) 

Expression:

  if ( Dummydim = 1  ,  Sum(value)  , sum ({<filters>}value)

You can hide the dummyfield in the chart for showing just one dimension 

[BrentSmits · Accepted solution]

Hi Sunny 🙂

It's nice to see you. You helped me in the past quite a few times and I can always trust on your knowledge and expertise. I tried to setup an example file, but this was not working out the way I wanted it to be. However, I got an idea from an external consultant in changing the data model which could perhaps provide a solution for this case. Working with the calculated dimensions with millions of rows of data, will most likely result in performance issues. Perhaps it would indeed be better to get this handled in the data model so that the expressions and dimensions in the chart are "a piece of cake". 

Thank you though and good luck!

Hello Qliksus. 

I think you are misunderstanding my question. I don't think creating the 2 different set of dimensions is the problem or showing them, the problem is that I have to take the difference between those values calculated in the 2 different sets of calculated dimension.

I'm afraid your solution won't work.

Thank you for your help though! Good luck!