topic Max invoice date in a set analysis expression in QlikView

Max invoice date in a set analysis expression

alex59800 — Mon, 18 Jan 2010 16:25:31 GMT

Hi all

I have a datamart with invoice dates. My actual max invoice date is 15/01/2010.

I would like to obtain sales amount for the max date above, which would be unchanged whatever my selections are:

for example if i select year 2009, it will give me sales amount for 15/01/2010 anyway.

i tried this but it's not working and i can not figure out why :

=sum({$<[INVOICE DATE]={'$(vDatemax)'}, YEAR={$(=Year(vTodaysDate))}>}[SALES AMT])

where vDatemax=max([INVOICE DATE])

and vTodaysDate=num(Today())

when i select year 2010 all is ok, but when i select 2009 or 2008, the answer is 0.

thx a lot for your help

Max invoice date in a set analysis expression

Mon, 18 Jan 2010 16:32:09 GMT

You need to ignore the selection on year. When you select 2009 or 2008, you are telling QlikView to pull values where the year is 2009 (the selection) and the year is 2010 (from your Today's Date variable), which can obviously never happen.

Try using the 1 in your Set Analysis instead of $:

=sum({1<[INVOICE DATE]={'$(vDatemax)'}, YEAR={$(=Year(vTodaysDate))}>}[SALES AMT])

EDIT: Do you really need the Year in your Set Analysis expression? You're already pulling for the Max of Invoice Date, so that should give you 15/01/2010. Try it without the Year and see if that works.

The reason I ask is, because using the 1 instead of the $ will ignore all selections. If you'd simply like to ignore the Year selection, you can use:

=sum({$<[INVOICE DATE]={'$(vDatemax)'}, YEAR=>}SALES AMT])

Max invoice date in a set analysis expression

Mon, 18 Jan 2010 16:35:17 GMT

Try this

=sum({1<[INVOICE DATE]={'$(vDatemax)'}, YEAR={$(=Year(vTodaysDate))}>}[SALES AMT])

Nimish

Re. :Re: Max invoice date in a set analysis expression

alex59800 — Mon, 18 Jan 2010 16:45:08 GMT

hi first of all thanks a lot for your prompt answer,

first expression does not work, it results in a 0 answer when i select year 2009 or 2008, i would like the answer to be unchanged whatever the year selection is (15/01/2010 sales amiunt is 25 300€)

second expression either, it results in giving me the sales amount for max date of 2009 or 2008 when i select those years, and i would like to have 15/01/2010 sales amount unchanged.

Any idea ?

Re. :Re: Max invoice date in a set analysis expression

alex59800 — Mon, 18 Jan 2010 16:49:35 GMT

may be the problem is coming from my variables ?

Re. :Re: Max invoice date in a set analysis expression

Miguel_Angel_Baeyens — Mon, 18 Jan 2010 16:54:42 GMT

I'd try

=sum({$<[INVOICE DATE]+={'$(vDatemax)'}, YEAR={$(=Year(vTodaysDate))}>}[SALES AMT])

Note the "+=" operator instead of "=".

Re. :Re: Re. :Re: Max invoice date in a set analysis expression

alex59800 — Mon, 18 Jan 2010 17:04:15 GMT

Not working either

i get sales amount for full year 2010, not for specific max invoice date 15/01/2010.

😞

Re. :Re: Re. :Re: Max invoice date in a set analysis expression

Mon, 18 Jan 2010 17:15:26 GMT

The best way to check is to create a chart and use your Set Analysis expression, but don't give it a label. Then, when the chart is rendered, the label will be your expression with your dollar sign expansions evaluated. That should show you what your variable is returning.

I thought you said your original expression was working if you selected 2010. The expression I gave is a simple substitution; if you're getting results with the $, you should get those results and more with the 1.

Re. :Re: Re. :Re: Max invoice date in a set analysis expression

Miguel_Angel_Baeyens — Mon, 18 Jan 2010 17:33:23 GMT

Give a try to (untested)

=sum({$ + 1< [INVOICE DATE] = {"$(vDatemax)"} >} [SALES AMT])

Re. :Re: Re. :Re: Re. :Re: Max invoice date in a set analysis expression

alex59800 — Mon, 18 Jan 2010 18:07:48 GMT

not working either, return me sales for all invoice dates 😞

Re. :Re: Re. :Re: Re. :Re: Max invoice date in a set analysis expression

Mon, 18 Jan 2010 18:23:36 GMT

What does your expression look like when the dollar sign expansion is evaluated? Have you created a working expression with the max invoice date explicitly entered?

=sum({1<[INVOICE DATE]={'15/01/2010'}>}[SALES AMT])

Does the above expression work?