topic Re: Converting if/then to set analysis in QlikView

Converting if/then to set analysis

Tue, 05 Feb 2013 15:33:34 GMT

I am trying to convert this perfectly working If/Then to set analysis:

=Sum(If(Date >= DateEffStart and Date<= DateEffEnd, Aggr(Sales,SalesIndex)))

I have tried the following which almost works, but appears to select the wrong dates: (it is searching through a slowly changing date table)

=Sum({$<[Date]={">=$([DateEffStart])<=([DateEffEnd])"}>} Aggr(Sales,SalesIndex))

Is this even possible with set analysis? The if/then does work perfectly.

Thanks very much for any insight!

Re: Converting if/then to set analysis

Anonymous — Tue, 05 Feb 2013 15:36:01 GMT

try this

=aggr(Sum({$<[Date]={">=$([DateEffStart])<=([DateEffEnd])"}>}Sales),SalesIndex))

Re: Converting if/then to set analysis

Caique_Zaniolo — Tue, 05 Feb 2013 15:37:17 GMT

Have you tried this?

Sum(Aggr(Sum( {$<[Date]={">=$([DateEffStart])<=$([DateEffEnd])"}>} Sales),SalesIndex))

Re: Converting if/then to set analysis

Tue, 05 Feb 2013 15:45:27 GMT

Unfortunately this did not work. I tried it. No value returned.

Re: Converting if/then to set analysis

Tue, 05 Feb 2013 15:51:19 GMT

This is working the same way as the one i first posted. It is returning the right sum, unfortunately when applying the total to other dimensions it seems to grab the first diminsion as opposed to the dimension of the date.

For example, if we are dealing with:

Office DateEffStart DateEffEnd

Miami 1/1/2011 6/1/2011

Chicago 6/2/2011 1/1/6099

Then totaling up the sales number by comparing the sales Date to the EffStart and end dates, if i using OFFICE as the dimension and the date on all the sales is 6/2/2011 or greater, the Office is coming back as Miami. It should be Chicago. The if/then format correctly returns the office Chicago on the Sum(sales) number.

I am not sure why set analysis seems to just grab the first value, Miami in this case, as opposed to returning Chicago.

Thanks SO much for your help everyone!

Re: Converting if/then to set analysis

chematos — Tue, 05 Feb 2013 19:05:54 GMT

Try this:

=aggr(Sum({$<[Date]={'>=$(=[DateEffStart]) <= $(=[DateEffEnd])'}>}Sales),SalesIndex))

Make sure format date fields is the same for each one