Solved

Running total

  • 6 November 2023
  • 5 replies
  • 111 views
P
Rank
Badge

I can’t seem to find a solution to my problem so I hope anyone of you knows an answer to this. 

So the situation is this: I have a view/cube were I have certain parameters( Hal, year, quarter and some results) (see image)

In this I use the first row to display the totals of those 4 quarters, which I calculate in my sql environment. (Because I was unable to do so in Thinkwise, seeing as he kept adding all those percentages instead of calculating the percentage of said total, even with a calculated field where I specifically told him to use the results in those totals to calculate it)

So this question might be a multiple one. The first being, isn’t there a way to do this in thinkwise so that my results are what they should be? So in this case 0.06% instead of 0.25 which I get now. (Because I need to have this resolved before I can use whatever answer you give me on the next question)

And the second question is if I replace my total row with a running total row calculated in thinkwise how can I make the program update said result if, for instance, I say with a prefilter I don’t want to display the 4th quarter, but I want the total to be recalculated on the 3 quarters that are showing. 

Or is this not possible? 

 

 

icon

Best answer by Vincent Doppenberg 6 November 2023, 11:38

View original
This topic has been closed for comments

5 replies

V
Rank
Userlevel 6
Badge +4

Hello Phil,

Does it work as expected if you define your ‘Procent’ cube field like this?

Note: the [Afkeur] and [Productie] strings are references to other cube fields. These references are made by placing the upper camelcase version of the cube field id between square brackets. So afkeur becomes [Afkeur] and my_cube_field becomes [MyCubeField].

If so, then your second question should be answered as well as it should automatically recalculate the value when you filter the data set.

I hope this helps.

P
Rank
Badge

I’m on an older version so I had it as (Sum (t1.afkeur) / Sum (t1.productie)) *100.0 but I tried it your way and that doesn’t work. Well, it does work, as in it calculates something the result for the percentage just isn’t right.

When looking at the image in my first comment the total percentage is 0.06 yet this method gives me 0.12

Although while before I could say it just added all the percentages I don’t really know how it gets that 0.12 now...

V
Rank
Userlevel 6
Badge +4

Hello Phil,

If you double click the 0,12 cell, it will open a drill down window that shows you the underlying data on which it based the value. Looking at this data and the formula for the cube field, can you see how it might arrive at the 0,12 value?

P
Rank
Badge

Well, after calculating the average, median, range etc I came to the conclusion it’s showing me the maximum. Seeing as that is 0.118, which rounded up is 0.12

 

So I’m setting this to average:

And then it does the trick. So for me this is solved.

Thank you for your help!

P
Rank
Badge

Hello Phil,

Does it work as expected if you define your ‘Procent’ cube field like this?

Note: the [Afkeur] and [Productie] strings are references to other cube fields. These references are made by placing the upper camelcase version of the cube field id between square brackets. So afkeur becomes [Afkeur] and my_cube_field becomes [MyCubeField].

If so, then your second question should be answered as well as it should automatically recalculate the value when you filter the data set.

I hope this helps.

 

An update on this, it turns out that this:

Note: the [Afkeur] and [Productie] strings are references to other cube fields. These references are made by placing the upper camelcase version of the cube field id between square brackets. So afkeur becomes [Afkeur] and my_cube_field becomes [MyCubeField].

does not work. my_cube_field only works as my_cube_field instead of MyCubeField. 

@Robbert van Tongeren wanted me to make this update so here we are.

 

Terms & ConditionsCookie settings