Hello. I’m working with dynamic production datasets from our MRP. I’m pulling most of the data for the visuals from a ‘fact_material_transactions’ table. My visuals thus far focus on:
work week integer calculated field at the dataset level, dependent on transaction_date [datetime],
serial_number [string],
part_number [string],
transaction_type_name [string]
Set Up:
My graphs over time (work week integer) are clustered bar combo charts showing planned (line) vs actual (bars); x-axis is work week integer, bars are serial_number (count distinct), group color is part_number, line is planned (Min) which is fed by a joined excel doc with static data. Picture of an example:
Problem:
I’d like to show cumulative bar combo charts next to these charts I introduced to you above. I’ve narrowed in on combinations of maxOver, minOver, runningSum, and windowSum. I can get the values to show properly in a table using
maxOver(runningSum(count({part_number})/count({part_number}),[{Work Week Integer} ASC],[{part_number}]),[{Work Week Integer}])
But that doesn’t translate to a bar chart visual due to the references needed. I’ve tried the following, as well, but can’t seem to get it:
windowSum(distinct_count({serial_number}),[{Work Week Integer} ASC],39,27,[{Work Week Integer}])
runningSum(distinct_count({serial_number}),[{Work Week Integer} ASC],[{Work Week Integer}])
Brief: Focusing on Q3, I want WW27 to show a bar with the value 78, then WW28 would show 200 (WW27 + WW28, 78 + 122), then WW29 would show 318, etc. I am filtered for a singular part number and there is one more filter that is supposed to take the first completion based on transaction_type_name at the dataset level:
ifelse({transaction_date}=minOver({transaction_date},[LEFT({serial_number},9)],PRE_AGG),{transaction_type_name},null)
Sadly the formula doesn’t seem to consistently work, but that’s the least of my concerns right now. Can anyone assist? Also, I’d love if there was a resource that dumbed down some of these explanations on Amazon’s documentation pages. I understand how LACs work conceptually, but when you start bringing in PRE_FILTER, PRE_AGG, POST_AGG_FILTER it’s not intuitive as to whether these levels are addressed at the dataset level or the visual level. Maybe I’m just dense. Thanks for the assistance.