Total sum of simulated values

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

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

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

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

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

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?

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

Hi Ole,

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

Best regards,

Jonas

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