Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the below logic where based on the Pay_frequency the Amount is being calculated for each subID,Expense Type and ExpenseID.
Aggr((Max([Amount]) * Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), 12, 4, 1, 2,1)),
subID,[Expense Type],ExpenseID))))
Now I am having a scenario where One subID doesnt have Amount for all 12 months ...it only has amount for 5 months....but based on the logic it is picking the Max of amount and multiplying by 12 as the Frequency is Monthly.How can I avoid this scenario ?
Thank mush for your help.
How about this:
=Sum(Aggr((Max([Amount]) *
Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), Count(DISTINCT [Month of Expense]), 4, 1, 2,1)),SubID,[Expense Type],ExpenseID))
Would you be able to share a sample to test out some possibilities?
Sure stalwar1 .
PFA Excel and the QVW.If you see SubID 500 it only has 4 months of data but because of the frequency Type it is multiplying it 12 times.
Thanks much for all your help.
How about this:
=Sum(Aggr((Max([Amount]) *
Pick(Match(pay_frequency, 'Monthly', 'Quarterly', 'Annually', 'Semi Annually','One Time'), Count(DISTINCT [Month of Expense]), 4, 1, 2,1)),SubID,[Expense Type],ExpenseID))
That works....thank yiu