topic Re: Logic help/Thoughts please. in QlikView

Logic help/Thoughts please.

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Logic help/Thoughts please.

sunny_talwar — Tue, 16 Aug 2016 14:22:30 GMT

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

Re: Logic help/Thoughts please.

Tue, 16 Aug 2016 14:48:20 GMT

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.

Re: Logic help/Thoughts please.

sunny_talwar — Tue, 16 Aug 2016 16:08:06 GMT

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

Re: Logic help/Thoughts please.

Tue, 16 Aug 2016 16:44:00 GMT

That works....thank yiu