topic Re: max date and max -1 in QlikView

max date and max -1

yogitamehta10 — Tue, 10 May 2016 13:47:38 GMT

HI All,

I'm getting date added in my sheet ramdomly.

i want to calculate sum of sales where date is maximum date and 1 less from maximum ,

so latest date and last lastest date.

how should i define it.

22/04/2016

28/04/2016

01/05/2020

Re: max date and max -1

sunny_talwar — Tue, 10 May 2016 13:50:41 GMT

May be like this:

Max Date

Sum({<Date = {"$(=Date(Max(Date), 'DD/MM/YYYY))"}>} Sales)

Max Date - 1

Sum({<Date = {"$(=Date(Max(Date, 2), 'DD/MM/YYYY))"}>} Sales)

Max Date and Max Date - 1

Sum({<Date = {"$(=Date(Max(Date, 2), 'DD/MM/YYYY))", "$(=Date(Max(Date), 'DD/MM/YYYY))"}>} Sales)

Re: max date and max -1

avinashelite — Tue, 10 May 2016 13:52:33 GMT

try like this

sum({<date_field={"=max({1}date_field)"}>}sales)

sum({<date_field={"=Date(max({1}date_field)-1,'DD-MM-YYYY')"}>}sales)

NOTE : change the 'DD-MM-YYYY' format according to your date format

Re: max date and max -1

yogitamehta10 — Wed, 11 May 2016 10:11:09 GMT

Hi,

It is working but showing result of max +max-1 .

sum({<startDate={"=Date(max({1}startDate)-1,'DD/MM/YYYY')"}>}data)

i'm using this for last max -1

Re: max date and max -1

yogitamehta10 — Thu, 19 May 2016 13:25:31 GMT

Hi Avinash,

This is not working perfectly .

sum({<date_field={"=Date(max({1}date_field)-1,'DD-MM-YYYY')"}>}sales)

this is giving result as sum of max and max-1 .

Re: max date and max -1

yogitamehta10 — Thu, 19 May 2016 13:29:27 GMT

Hi sunny,

Max date-1 is not working .

Re: max date and max -1

sunny_talwar — Thu, 19 May 2016 13:32:58 GMT

Try this:

Sum({<date_field={"$(=Date(Max({1}date_field)-1,'DD-MM-YYYY'))"}>}sales)

Re: max date and max -1

yogitamehta10 — Mon, 23 May 2016 09:15:05 GMT

Hi Sunny,

This is not working. Could you please help with something?

Re: max date and max -1

sunny_talwar — Mon, 23 May 2016 10:31:43 GMT

Would you be able to share a sample where this isn't working for you?

Re: max date and max -1

yogitamehta10 — Mon, 23 May 2016 10:48:03 GMT

ok.. Please check

Re: max date and max -1

sunny_talwar — Mon, 23 May 2016 10:49:57 GMT

I am not seeing anything attached?

Re: max date and max -1

yogitamehta10 — Mon, 23 May 2016 10:50:56 GMT

Please check

Re: max date and max -1

sunny_talwar — Mon, 23 May 2016 10:57:48 GMT

In your attached sample, I see three dates

22/04/2016

28/04/2016

19/05/2016

If Max(Date) = 19/05/2016, then Max(Date) - 1 = 18/05/2016. Since the date is non-existent, you won't see anything

Do you want 1 week ago?

Max(Date) - 7? But for the current sample that won't work because there is no 12/05/2016 in your sample.

or may be you want Max(Date, 2)? This will give you 28/04/2016. It all depends on what you want and what you have. If you can let me know what you want, I can give you a final expression

Re: max date and max -1

yogitamehta10 — Mon, 23 May 2016 11:01:11 GMT

Actullly it would be a randon date it could be a week before or 5 days before , so i want my chart to pick current latest date as current week and 2nd latest date as last week

Re: max date and max -1

sunny_talwar — Mon, 23 May 2016 11:04:48 GMT

May be this:

Sum({<date_field={"$(='>=' & Date(Max({1}date_field)-7,'DD-MM-YYYY') & '<' & Date(Max({1}date_field)-0,'DD-MM-YYYY'))"}>}sales)

For the sample above, this will pick all the days which are greater than or equal to 12/05/2016 and less than 19/05/2016. Is there a possibility that you might get multiple dates within that range? or will you get only one date a week?

Alternatively, you can also try this:

Sum({<date_field={"$(=Date(Max({1}date_field, 2),'DD-MM-YYYY'))"}>}sales)

Re: max date and max -1

yogitamehta10 — Mon, 23 May 2016 11:37:50 GMT

second one seems to work fine .. Thanks a lot, i will test this by adding more data and let you know

Re: max date and max -1

sunny_talwar — Mon, 23 May 2016 11:50:43 GMT

Yup, keep us posted

Re: max date and max -1

yogitamehta10 — Mon, 23 May 2016 14:24:50 GMT

one more thing .. if i change my requiremnt to current month and last month

so that means 19th of today data will be current month and 19th of last month data would be last month

how i will implemnt this in same thing.

Re: max date and max -1

sunny_talwar — Mon, 23 May 2016 14:28:33 GMT

A single date or the whole month? or month to date?

Also, please try to refrain yourself from posting new requirement in an old thread. Makes it difficult for people looking for similar requirements in the future. Opening a new thread will not just benefit you (because you will get all the new audience) + it will help this community.

Re: max date and max -1

yogitamehta10 — Mon, 23 May 2016 15:33:57 GMT

Single date