topic Re: Issue in Set Analysis with aggr in QlikView

Issue in Set Analysis with aggr

Anonymous — Thu, 13 Jul 2017 11:57:59 GMT

Dear Friends

I am using below expression to calculate the MTD which works correctly.

NUM(Count(DISTINCT{<Day={"<=$(=Day)"},ZONE={'EAST'},PRODUCT_BOOKING_STATUS={'BOOKED','UNDECIDED'},SUB_BUSINESS={'DOMESTIC'}>} LTCS.BFN))

However when I use the same expression for MTD in an expression which has aggr it does not work correctly.

Kindly let me know if there is any mistake or changes need to do to achieve the result.

sum(aggr(sum(DISTINCT {<ZONE={'EAST'},PRODUCT_BOOKING_STATUS={'BOOKED','UNDECIDED'},SUB_BUSINESS={'Domestic'},>} NO_ADULT),LTCS.BFN))

I am using these expressions in Text Object.

Thanks in advance.

Regards

Chintan

Hello Sunny I am able to get correct result by passing the modifiers before aggr as suggested by you. However the same thing is not working for the attached code in text obj as it is bit complex. Is there any way around to get the MTD result in atached code. Thanks & Regards Chintan

Message was edited by: Chintan Gala

Re: Issue in Set Analysis with aggr

sunny_talwar — Thu, 13 Jul 2017 12:00:04 GMT

May be try this

Sum({<Day={"<=$(=Day)"}, ZONE = {'EAST'}, PRODUCT_BOOKING_STATUS ={'BOOKED','UNDECIDED'}, SUB_BUSINESS = {'Domestic'}>} Aggr(Sum(DISTINCT {<Day={"<=$(=Day)"}, ZONE = {'EAST'}, PRODUCT_BOOKING_STATUS ={'BOOKED','UNDECIDED'}, SUB_BUSINESS = {'Domestic'}>} NO_ADULT),LTCS.BFN))

Re: Issue in Set Analysis with aggr

Anonymous — Thu, 13 Jul 2017 12:23:11 GMT

Hello Sunny

It gives the expected result.

Re: Issue in Set Analysis with aggr

Anonymous — Wed, 19 Jul 2017 12:36:12 GMT

Thanks & Regards

Chintan

Re: Issue in Set Analysis with aggr

sunny_talwar — Wed, 19 Jul 2017 12:39:04 GMT

bit complex .... I have no idea what it's doing brother... especially looking at just the expression doesn't really tell me much... I wish I could help you, but without a sample it would be nearly impossible to decipher your expression

Re: Issue in Set Analysis with aggr

Anonymous — Thu, 20 Jul 2017 04:21:50 GMT

Sure, I will try something and if it does not work I will try to put a sample attachment with reduced data if possible.

Re: Issue in Set Analysis with aggr

Anonymous — Fri, 21 Jul 2017 10:09:44 GMT

Hello Sunny

I have attached sample qvw with reduced data with a couple of text objects.If it provides some clarity to achive the MTD result.

Thanks & Regards

Chintan .