I have a master date, along with master year, month, and quarter that I'm attempting to use for another set of date fields, Book_Year, Book_Month, and Book_Quarter in selecting data. In the application attached, see the Turn Time chart, the data is showing all possible values, instead of for Booked_Month. when only selecting from Master Year and Master Month. Master Dates can be very confusing, so I've tried to simplify what I'm attempting to do.
The purpose of the below expression is to calculate the total of the difference of MSPD Date added and Booked Date, either by Sales Order Lines or Serial Numbers. It 'seems' to be working for SO Lines, but not Serial Number. I've only noticed the issue when grouping by Serial Number.
I am using this expression:
if(Toggle = 'SO Lines',
sum(
aggr(
sum( {< [MSPD Date Added] = {"*"}, [Booked Date] = p(Master_Date) >} DISTINCT num(([MSPD Date Added])) - num(([Booked Date])))
,_orderlinecompkey)),
sum(
aggr(
sum( {< [MSPD Date Added] = {"*"}, [Booked Date] = p(Master_Date) >} DISTINCT num([MSPD Date Added]) - num([Booked Date]))
,[Serial Number]))
)
The Master Date filters aren't working as I'd hoped and the data only seems correct, when I use the specific Booked Date filters. I can hide the Master Date filters for this chart if necessary and only use the Booked Date filters but had hoped to use a Master Date and it work for all charts.
I've also attached screen shots as additional reference.
