Hello everyone,
In the Line Chart, we are having a problem with a calculated metric. The calculated metric has a logic of “ifelse” in which it validates the data of a selector to return one data or another.
in which it validates the data of a selector to return one data or another.
The problem is that the data is calculated with a “running sum” and it throws the following error:
sourceErrorCode: VISUAL_CALC_REFERENCE_MISSING
sourceType: SPICE
I tried changing the running sum by a SumOver and there is no error, but we need to calculate the accumulated data (that’s why the RunningSum).
PS: The selector is also calculated, it takes the value of the control bar.
Can you post your calculated fields and field wells on here?
The Visual Calc Refrence Missing is likely because a field present in the runningSum calculation isn’t added to the visual.
So if you have runningSum( {measure}, [{date} ASC], [{GroupBy}]) you need to include {GroupBy} in the grouping field well
1 Like
I leave more detail about the problem we are having.
As you can see in the image, we have a control that we use as a selector to display a data and a dimension.
The process starts when a dimension is selected (1), that selection is passed by parameter to the calculated metric (2) and the dynamic dimension (3).
The formula of the metric is the following:
ifelse(
${dynamic dimension}=‘Dimension 1’,runningSum( sum({amount}), [Date ASC], [{1}]), <— the first line should be resolved
${dynamic dimension}=‘Dimension 2’,runningSum( sum({amount}), [Date ASC], [{2}]),
${dynamic dimension}=‘Dimension 3’,runningSum( sum({amount}), [ASC Date], [{3}]),
0
)
Formula detail:
When I select the “Dimension 1” from control (1), the first line should be resolved, but for some reason when we use the “runningSum” function, the parameter data is lost. This does not happen when we use the function “SumOver” where we check that it does resolve the parameter data.
As last step in the image (3) we also receive the parameter data from the selector.
The problem is when you are calling {1} in the group by for the runningSum, it would need {1} in the dimension field well of the visual (because runningSum is a table calculation). A New calc field {Groupby} I posted below is a workaround
Try this:
If the values on the control aren’t exact matchs of the values you are trying to group by, create a calculated field like:
{Groupby}:
ifelse(${dynamic dimension} = ‘Dimension 1’, {1},
${dynamic dimension} = ‘Dimension 2’, {2},
${dynamic dimension} = ‘Dimension 3’, {3},
NULL
)
Add {Groupby} into the “Color” field well
and then use {Groupby} in a formula like:
runningSum(sum({amount}), [Date ASC], [{Groupby}]
And add this formula to the “Value” field of your visual
If the values in the ${dynamic dimension} control are Exact matches to the field values, then this alone should work:
runningSum(sum({amount}), [Date ASC], [{$dynamic dimension}]
1 Like
Thanks for the answer, unfortunately we still have the problem.
We have the calculated field that you comment. But apparently that solution doesn’t work with the RunningSum function.
We verify that I change the RunningSum function to SumOver (With the same partition) in that case if it correctly resolves the parameter “Select Dimension” (1) which is the one that groups (Group by)
We changed the SumOver to RunningSum again and again we have the same problem. So we deduce that the problem is from the RunningSum function.
I remodeled your dataset using simple values for {1} {2} and {3}. Note, the {end} is synonymous with your {Amount}.
I followed the steps I posted above and the visual renders with no problems:
Make sure the Date field, and the calculated group by field I mentioned earlier are the fields being used in the running sum, and in the field wells of the visual. If they are different it will error.
Please make sure your visual resembles the screenshots included. If you are still having issues, please include a screenshot of the calculated fields, field wells, and small portion of your dataset so I can further review.
3 Likes
Thank you very much MKuhns01, Your answer solved my problem.
1 Like
Thanks @MKuhns01! Glad your problem is now solved @diego.agrology!
1 Like
