Max invoice date in a set analysis expression

[alex59800]

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

[#2]

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

[#3]

Try this

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

Nimish

[alex59800]

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 ?

[alex59800]

may be the problem is coming from my variables ?

[Miguel_Angel_Baeyens]

I'd try

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

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

[alex59800]

Ni

Not working either

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

😞

[#8]

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.

[Miguel_Angel_Baeyens]

Give a try to (untested)

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

[alex59800]

hi

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