Logic help/Thoughts please.

[#1]

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.

[sunny_talwar]

Would you be able to share a sample to test out some possibilities?

[#3]

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.

[sunny_talwar · Accepted solution]

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))

[#5]

That works....thank yiu