Set Analysis restriction not working

[#1]

HI ,

I have the below issue with expression.Pls help:

In the enclosed doc.I have used the used the expression:

=sum({$<CALENDAR_DATE={'>=$(=DATE(VSTARTDATE))<=$(=DATE(VENDDATE))'},GC_TRANS_ID={$(=AGGR(MAX(GC_TRANS_ID),GC_CARD_NUMBER))}>}

GC_BALANCE)

Here in the above expression I want the calendar_date to be restricted wrt VENDDATE and then get max of GC_TRANS_ID wrt to GC_CARD_NUMBER .But as highlighted in the enclosed doc.

For GC_CARD_NUMBER:6103300000869935, there are 3 calendat_dates with gc_trans_ids as

:02/14/2011-105237833,

03/12/2011-105284498

05/18/2011-105373479

As per the restriction on the calendar_date it should pick gc_trans_id as 105284498 but its picking 105373479 as per the grouping by gc_card_number.But if I want to restrict it by calendar_date how do I achieve it.Is there anything that needs to be added to the expression to get it.

Thanks,

Swetha

[#2]

Is It bug in qlikview?Is there a way to achieve this?

[erichshiino]

Hi,

Sometimes the date format doesn't work well on set analysis.

That's why, many times, I convert the dates to a secondary integer field to make set analysis that you work for sure.

In your script analysis, on the table with CALENDAR_DATE you can make another field with

Load ... // your regular load script

CALENDAR_DATE,

NUM(CALENDAR_DATE) AS nDate

resident / From...

Then, on set analysis, it would be like this:

sum({$<nDate={'>=$(=num(VSTARTDATE))<=$(=num(VENDDATE))'},GC_TRANS_ID={$(=AGGR(MAX(GC_TRANS_ID),GC_CARD_NUMBER))}>}

GC_BALANCE)

Hope it helps,

Erich

[#4]

Hi Erich,

Thank You for the reply.

I am getting the answer as '$0.00'.As explained in the above example,max(gc_trans_id) is taken as "105373479" but I want it to select "105284498" as this corresponds to the date range specified.But trans_id "105373479" to date '05/18/2011 which is outside my date range selection.

Thanks,

Swetha

[erichshiino]

Hi,

Did you try to restrict the max on the aggr ?

It`s calculated before you apply the restriction on calendar_date. It can create your diffence.

Try this:

=sum({$<CALENDAR_DATE={'>=$(=DATE(VSTARTDATE))<=$(=DATE(VENDDATE))'},GC_TRANS_ID={$(=AGGR(MAX({ < CALENDAR_DATE={'>=$(=DATE(VSTARTDATE))<=$(=DATE(VENDDATE))'} >}GC_TRANS_ID),GC_CARD_NUMBER))}>}

GC_BALANCE)

[#6]

Thanks Erich.The formula is working .But its working If I select only one particular giftcard and when I select the date range irrespective of selection of giftcard its  giving "0".Please see the enclosed doc.I have included the 2 scenarios.Any suggestions would be appreciated.Not sure y its giving this result.

Thanks,

Swetha

[erichshiino]

The problem is probably your AGGR.

I thought it was ok because you used it before.

Basically, it generates one result for each  GC_CARD_NUMBER (gift card?) so if there is more than one GC_CARD_NUMBER it will not work for set analysis.

maybe you shouldn`t use it. Like this:

(MAX({ < CALENDAR_DATE={'>=$(=DATE(VSTARTDATE))<=$(=DATE(VENDDATE))'} >}GC_TRANS_ID)

or get only one value from the many results of aggr, like this:

max(

AGGR(MAX({ < CALENDAR_DATE={'>=$(=DATE(VSTARTDATE))<=$(=DATE(VENDDATE))'} >}GC_TRANS_ID),GC_CARD_NUMBER) )

but it will depend on your data model and the result you want.

Regards,

Erich

[#8]

so, there is no  way to get for each gift card usibng aggr?

[erichshiino]

It`s difficult to tell because I don't know what you are trying to calculate.

Maybe you could use aggr outside of everything

sum ( aggr ( sum({$<CALENDAR_DATE={'>=$(=DATE(VSTARTDATE))<=$(=DATE(VENDDATE))'},GC_TRANS_ID={$(=(MAX({$<CALENDAR_DATE={'>=$(=DATE(VSTARTDATE))<=$(=DATE(VENDDATE))'}>} GC_TRANS_ID)))}>}

GC_BALANCE) , GC_CARD_NUMBER))

Sorry if I forgot any ( or )  or {}

[#10]

Thanks once again for the reply.All am calculating is just the balance on each card as of restricted /selected date range.If any transactions happen on the card after the selected date my report should not select it but as of specified date range only it should give the remaining balance on the card.And this report is doing good on sql server and now iam trying to translate it to qlikview.But its not working.Any suggestions would be appreciated..

Thanks,

Swetha