Total sum of simulated values

• 

  Ole Dyring

  January 31, 2023 15:06

  Hi Jonas,

  I assume your Grand Total, the first row in crosstab, is just a standard Grand Total - and not one you calculated?

  If your Total oms calculation is something like this:

  if sum(d1, 0, m1) < 250000 then 250000 else sum(d1, 0, m1)

  Then I don't see why it shouldn't work.

  Maybe you will need to provide more details.

  BR / Ole

  0
• 

  Jonas Beuschau Gudsø

  January 31, 2023 15:54

  Hi Ole,

  The grand total is standard and the total oms calculation is as you describe. The problem is that in my simulation, I have increased the Total oms of rows less than 250.000 to 250.000 as minimum. Thus, the total oms must be an increased value, but now it equals the "Oms Firmavaluta" because 8.964.324 > 250.000. It calculates each row seperately, but only "normal" rows should be calculated. The standard Grand total should just summarize the rows.

  

  You can see that the last rows have different values in first and last column.

  Best regards,

  Jonas

  0
• 

  Ole Dyring

  February 01, 2023 07:57

  All right,

  Now I get it. You want the Grand Total of 'Total oms' to be a sum of the calculated values, of course.

  I suggest that you create an additional calculated column to fix the issue. In my example I assume that the 'Total oms' calculation is your first calculated column and thus can be referred as 'c1'. Possibly it is actually your second calculated column and thus should be referred as 'c2'.

  Anyway, the extra calculated column should use a syntax like this:

  if allcount(d1, all(c), 0) = 0 then sum(c1, 0, 0) else sum(c1, all(c), 0)

  This should give the correct Grand Total while keeping the individual row values. The original 'Total oms' calculation should probably now be hidden (using Visibility from the Properties Smartpad).

  BR / Ole

  0
• 

  Jonas Beuschau Gudsø

  March 13, 2023 14:17

  Hi Ole

  I have tried your solution, but it does not solve the issue.

  

  At the lowest level, the figures are correct, but I need the Grand sum to be equal to the sum of "Toyo oms mellemregn." from lowest level. Right now, it equals the sum of 117.793.566, which is a dummy number that result from an if below amount calculation.

  

  I have tried different versions of your calculated column, all without the right result. Any ideas left?

  Best regards,

  Jonas

   

  0
• 

  Ole Dyring

  March 14, 2023 07:16

  Hi Jonas,

  I can't tell from the information i've got so far.

  Can you please provide screenshots of:

  • The crosstab in Definition mode.
  • The list of calculations in your Calculations tab.
  • The 'Visibility' settings.

  BR / Ole

  0
• 

  Jonas Beuschau Gudsø

  March 16, 2023 13:47

  Hi Ole,

  I understand it is a complex formula to create. Unfortunately, I cannot get access to the definition mode crosstab. It gives me an error.

  

   

  

  Is it possible to tell anything by the use of this information only?

  

  

  0
• 

  Ole Dyring

  March 17, 2023 10:52

  Hi Jonas,

  Please add a calculated column to the end of your crosstab: allcount(d1, all(c), 0)

  And show me a screenshot of your crosstab with the extra column.

  BR / Ole

  0
• 

  Jonas Beuschau Gudsø

  March 24, 2023 11:58

  Hi Ole,

  
  Can you use the following screenshot? There is no hidden values in it.

  Best regards,

  Jonas

  0
• 

  Ole Dyring

  April 05, 2023 06:37 (Edited April 05, 2023 06:38)

  Hi Jonas,

  If you enable "Land etiketter" and "Land subtotaler" from the Visiblity options, I think you will see that the subtotals are correct on this level.

  Since this additional level exists, you will have to add yet another calculated column. So going a couple replies back, you should still add (or keep) the calculation:

  if allcount(d1, all(c), 0) = 0 then sum(c1, 0, 0) else sum(c1, all(c), 0)

  Now, if the above calculation is your c2 calculation, you should add another calculated column:

  if allcount(d1, all(c), 0) = 0 then sum(c2, 0, 0) else sum(c2, all(c), 0)

  This is necessary due to the number of levels on the vertical axis.

  "Land etiketter" and "Land subtotaler" can be hidden again, and it should still work. The c2 calculation should be hidden too.

  BR / Ole

  0

Please sign in to leave a comment.