Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My goal is to, using chart level scripting, build a bar chart like this one:
My problem is that in this formula i'm using, although Q1 has the correct bar hight — meaning the bar does indeed represent the sum of January, February, and March, which amounts to USD 3.737.258,46 — the data label is not only completely incorrect but also improperly formatted. How should I change the script in order to display exactly "USD 3.737.258,46" and mantain the bar high?
My dimension has no label and is called "MÊS" and my Measure is a big formula, but labeled "Actuals". My Chart Level Scripting is:
Let P = HCNoRows();
Let vQ1Total = 0.0;
For J = 1 to P
Let vDim = HCValue(#hc1.dimension.1, J);
if Match(vDim,'Jan','Fev','Mar') then
Let vQ1Total = vQ1Total + HCValue(Actuals,J);
end if
Next
Let vQ1TotalFormatted = Num(vQ1Total, 'USD #.##0,00;- USD #.##0,00');
Add Load 'Q1' as "MÊS", num($(vQ1Total)) as Actuals Autogenerate 1;
Tried changing the last line to
Add Load 'Q1' as "MÊS", '$(vQ1TotalFormatted)' as Actuals Autogenerate 1;
Got the right label but not the right bar hight (stays at ground zero):
Tried also changing to
Add Load 'Q1' as "MÊS", dual('$(vQ1TotalFormatted)',$(vQ1Total)) as Actuals Autogenerate 1;
And got this error:
Decimal separators must be '.', not ','.
Since the numbers are so high, use floor to round down the value of the variable, so it becomes an integer.
Add Load 'Q1' as "MÊS", dual('$(vQ1TotalFormatted)',$(vQ1Total)) as Actuals Autogenerate 1;
Instead of $(vQ1Total), floor($(vQ1Total)).