Chart Script - Conditional Check of Dimension

[Jim_Ogilvie]

Hello,

I have successfully created a Chart Script that calculates the Year-Month variance in Margin% (see pic below).

I would like to tidy this column up by putting a Null value when the Month dimension changes, so "Feb" in "2020", for example would contain '-'.

I have tried to test the values of the 'Month' column, the current vs the previous as a trigger for blanking the Variance calculation, but it never equates to true:

         HCValue(Month,i) = HCValue(Month,i-1)

Where 'i' is the counter variable in my loop, but this is never true...

Chart Scripts are very new to Qlik, so there is not a great deal of knowledge base out there (@Michael_Tarallo could you or Venkat comment, his article took me this far).

Thanks in advance,

Jim

[Or]

You should be able to achieve this by putting the two values into variables and then comparing those. This is from a script I wrote for another purpose but should be appropriate here, though in your case you'll just need the comparison and a null value rather than the running total I used. Note that the code could probably be cleaner, this was just a proof of concept...

I think you could also just skip the entire thing and compare HCValue(#hc1.dimension.2, J) = HCValue(#hc1.dimension.2, J-1) rather than referencing the dimension name which I think doesn't work well, but I'm not sure here. Scripts are kinda frustrating to write/test...

Let P = HCNoRows(); // Get number of rows and set up required variable
Let vRunningTotal= 0;

For J = 1 to P
// In this example, we will add a running total by Dim1 and Dim2 but this can be adjusted to any number of dimensions with any names
Let vDim = HCValue(#hc1.dimension.1, J) & HCValue(#hc1.dimension.2, J); // Add the current row dimension values
Let vDimPrev = HCValue(#hc1.dimension.1, J-1) & HCValue(#hc1.dimension.2, J-1); // And the previous row values
// If the current row's dimensions match the previous row's, continue the running total
If vDim = vDimPrev then
Let vRunningTotal = vRunningTotal + HCValue(Sales,J);
else // If this row starts a new subtotal set, subtotal is just Sales
Let vRunningTotal = HCValue(Sales,J);
end if
// Put the calculated subtotal into the Subtotal column
Put RunningTotal(J) = vRunningTotal

Next;

[Jim_Ogilvie · Accepted solution]

Thanks for coming back. I stripped things back for simplicity to using the dimension name syntax. 🙂

I've managed to solve this using variables (as you suggested) in combination with MATCH().

I've just tested using the straight equality operator, this also works.

Many thanks

Jim