topic Show sales value for maximum date in QlikView

Show sales value for maximum date

Fri, 03 Feb 2012 12:30:30 GMT

Hi,

I'm trying to build an expression that will show sales value for one maximum date of the year (31.12) and user will be able to choose only year. So for example if I choose 2010 I want expression to show me the value for 31 December 2010.

How can I do that? Please help

Show sales value for maximum date

Fri, 03 Feb 2012 12:42:08 GMT

Hi,

would be something like

sum({<Date={'$(=max(Date))'}>} Sales)

Regards,

Björn

Show sales value for maximum date

tresB — Fri, 03 Feb 2012 12:48:29 GMT

Hi Paulina,

probably set analysis can help you out. write your expression somewhat like :

sum( {$<Date_month = {31-Dec}>} Sales ). This is possible when you have already created a field 'Date_month' at the backend with the similar('DD-MM') format.

Re: Show sales value for maximum date

tresB — Fri, 03 Feb 2012 12:55:40 GMT

Bjorn's way is perfect if you want it for maxdate. But if you are asking for specifically 31st Dec, then you have to go with my option.(Note: difference between these two is, if there is no transaction for 31st dec, my expression would return zero, while Bjorn's would find the next maxdate, may be 30th Dec or whichever available and return sales amount for that day).

Show sales value for maximum date

CELAMBARASAN — Fri, 03 Feb 2012 12:59:23 GMT

Hi,

You can try with this expression

Sum({<Date={'$(=YearEnd(MakeDate(Max(Year))))'}>} Sales)

Celambarasan

Show sales value for maximum date

Fri, 03 Feb 2012 13:05:12 GMT

Actually the 31 Dec is an example, not the fixed date I want to use. I don't know why but the expression gives me 0 as a result (the one proposed by Björn and ).

The thing is that I have accumulated data - for example December data= November data + December sales data, for November I have October + November etc. So I have to show data for the one, maximum date to have a value that gives me the year total. But I also want to have it if I select only quarter - then I want to show the data for the last day of the quarter. So the 31 december isn't an option for me.

Show sales value for maximum date

CELAMBARASAN — Fri, 03 Feb 2012 13:19:59 GMT

Hi,

You can try with this expression

Sum({<Date={'$(=MakeDate(Max(Year),Max(Month),Max(Day))'}>} Sales)

This may be your need

Celambarasan

Show sales value for maximum date

Fri, 03 Feb 2012 13:33:47 GMT

Thank you everyone for the help. I don't know why my expression always shows 0... Maybe I've done something wrong...

I found a solution for this. It's about creating a variable Maxdate=max(Date) and then writing the expression:

=sum(if(Date=MaxDate,Sales)).

Re: Show sales value for maximum date

sathishbabu_s_g — Wed, 22 Feb 2017 12:16:16 GMT

Hi,

You can also try below, where MaxDate is the variable of below expression

Let MaxDate = max(date);

=Sum({$<date = {'$(=date(MaxDate))'}>} Value)

Re: Show sales value for maximum date

sakshikaul — Tue, 03 Jul 2018 09:39:25 GMT

I need same thing to implement using calendar variables