Hello,
When I restrict the x-axis (time) to a quarter, my visuals work fine:
But when multiple quarters are shown, I get this result:
Syntax for bars:
runningSum(
distinct_count({part_number}), [{work_week} ASC], [{part_group}])
I can’t use ‘periodToDateSumOverTime’ because my x-axis is of string data type. I had to right-join the planned values onto the MRP data in order to get the line to extend beyond the existing data.
Thank you.
Hello @tjohnson !
Could you try using parse date on the date field you are using for the x-axis in a calculated field?
I’d still need to include ‘work_week’ in the field wells for the reference. I don’t think the format ‘YYYY.WW’ is supported, either, according to the documentation. – I just tried
parseDate({work_week},‘YYYY.WW’) and ‘We encountered an error trying to save your calculated field…’ error popped.
Hey @tjohnson !
Sorry to get back to you late.
Maybe I don’t totally understand the problem, is there something specific about the data or the visual that doesn’t look right compared to your expected outcome? This will help me understand your data better.
From what I can tell it seems like the runningSum is on the bars of your combo chart and because there is no data yet for 2024 that it is potentially saying 36 for each day after because that is the total.
What is your desired outcome/use case?
Hi Duncan. In the second picture you’ll see two quarters. I want the bars to not exist when then there’s no data for the week.
Another example I can use is the following (right-joined ERP data onto an Excel file to get planned vs actual data):
I don’t want the bar to show for a week that has no data. That’s counterintuitive for a “running sum” I realize, but perhaps there’s another function or combination of functions to solve this?
Desired:
Hey @tjohnson !
I believe I have the solution for this.
Keep this calc:
Calc1 = runningSum(distinct_count({part_number}), [{work_week} ASC], [{part_group}])
Then use an ifelse statement to determine if you return the value of Calc1 or 0:
Calc2 = ifelse(distinctCountOver({part_number}, [{work_week}], PRE_AGG) > 0, {Calc1}, 0)
The 2nd function should returned only values when your sum for that week is greater than 0. Let me know if you have any questions!
Hi Duncan.
Sorry for taking so long to respond. “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.” The solution doesn’t work.
Hello @tjohnson, were you able to find a solution to this issue? I think @duncan’s proposed solution was super close, the error is just throwing because it doesn’t like that the runningSum function and distinctCountOver are used together. Maybe you can do something else where you replace the returned values in Calc2 to return a random value like 1 if true or NULL if false. Then you can try filtering the visual based on calc2. My worry is this might completely eliminate the weeks from the graph rather than just displaying 0 as the value, but it is worth a shot! You could also attempt using distinct_count({part_number}, [{work_week}]) instead of distinctCountOver to see if that fixes the aggregation issue. Let me know if that helps or if you found an alternative solution. Thank you!
