topic Re: Last 5 Days in QlikView

Last 5 Days

priyarane — Sun, 26 Apr 2015 12:25:23 GMT

Hi Experts,

How to find last 5 days depending selection.

For Ex: I have below data.

Tr_Date	Sales
01-05-2015	10
02-05-2015	23
03-05-2015	34
04-05-2015	55
05-05-2015	12
06-05-2015	11
07-05-2015	22
08-05-2015	33
09-05-2015	45

Filter is Tr_ Date

Straight table: Dim - Tr_Date, Expression - ?

so If I select Tr_Date-6th then I need 2 to 6 sales.

Re: Last 5 Days

sunny_talwar — Sun, 26 Apr 2015 12:56:10 GMT

Use this in your set analysis:

{<Tr_Date = {">= $(=Date(Max(Tr_Date) - 4, 'DD-MM-YYYY')) <= $(=Date(Max(Tr_Date), 'DD-MM-YYYY'))"}>}

I think -4 should work to reach 2nd May, but if not try it with -5.

Best,

Sunny

Re: Last 5 Days

prabhas277 — Sun, 26 Apr 2015 13:10:30 GMT

Hi priya,

pls try the below expression if it works fine then give me correct answer

=sum({<Tr_Date={">=$(=max(Tr_Date))<=$(=max(Tr_Date)+4)"}>}Sales)

Regards,

prabhas

Re: Last 5 Days

priyarane — Sun, 26 Apr 2015 13:19:02 GMT

Hi Prabhas,

Its not working I am attaching my app

Re: Last 5 Days

priyarane — Sun, 26 Apr 2015 13:19:34 GMT

Hi SunIndia,

Its not working I am attaching my app

Re: Last 5 Days

sunny_talwar — Sun, 26 Apr 2015 13:33:48 GMT

Created a variable vVar = ='>=' & Date(Max(Tr_Date) - 4, 'DD-MM-YYYY') & '<=' & Date(Max(Tr_Date), 'DD-MM-YYYY')

and use this as your expression: =Sum({<Tr_Date = {'$(vVar)'}>}Sales)

Also find attached the application.

Best,

Sunny

Re: Last 5 Days

petter — Sun, 26 Apr 2015 13:38:49 GMT

A tweaking of SunIndia's suggestion should work for you:

Sum( {<Tr_Date = {"<=$(=Date(Num(Max(Tr_Date))))>$(=Date(Max(Tr_Date)-4)))"}>} Sales)

Leaving out the formatting parameter for the Date() function will default to the formatting of dates that is currently active for your settings on your computer.

Re: Last 5 Days

prabhas277 — Sun, 26 Apr 2015 13:41:04 GMT

please attach your application then it will very clear

Re: Last 5 Days

sunny_talwar — Sun, 26 Apr 2015 13:42:44 GMT

I was having a hard time understanding why the formula wasn't working. Petter Skjolden‌ do you know why the date formatting was creating a problem??

Best,

Sunny

Re: Last 5 Days

maxgro — Sun, 26 Apr 2015 13:53:49 GMT

Sum( {$ <Tr_Date = {">=$(=Date(Max(Tr_Date)-4))<=$(=Date(Max(Tr_Date)))"}>} Sales)

Re: Last 5 Days

Anonymous — Sun, 26 Apr 2015 13:53:50 GMT

It works for me, see attached. I used set from sunindia‌ with minor adjustments.

Re: Last 5 Days

petter — Sun, 26 Apr 2015 14:33:32 GMT

I guess it is because the Tr_Date will have the formatted date to whatever the client (or server) is set to as formatting and then try to compare it with the formatted dates in the search string - which will not work I guess ...

Whereas a Date-number should always be safe to compare since it is not subject to formatting settings. I guess the parsing routine for the search string will discover that it is purely date-numbers and then it will use the date-number of Tr_Date instead of it's formatted part.

Re: Last 5 Days

sunny_talwar — Sun, 26 Apr 2015 14:39:40 GMT

That sounds like a good reason, but my only concern is that what if during the load we have done something like Date(Tr_Date, 'DD-MM-YYYY'), in which case Date() function alone will depart from what has been in the load if the system date is different to DD-MM-YYYY, right?

It seems like a hit and trial method for dates within set analysis, sometimes something work and on other times the same solution won't work. I always struggle to use set analysis on a date field.

Sunny

Re: Last 5 Days

priyarane — Sun, 26 Apr 2015 14:56:08 GMT

Hi Sunindia,

Thanks its working.

msolomov‌is aslo said right.