topic Re: Date range in Set Analysis in QlikView

Date range in Set Analysis

Wed, 01 Oct 2014 07:53:39 GMT

Hi All,

I have here a table:

I need to create an analysis, what is the total Qty of the Report Date with the specific date range.

ex: For Report date 20-Sep-14, the range is >=14-SEp-14<=20-Sep-14

How can I include this in my set analysis?

Thanks!

Re: Date range in Set Analysis

anbu1984 — Wed, 01 Oct 2014 07:59:44 GMT

=Sum({<Report_Date={'>=$(=Max(DayStart)<=$(=Max(DayEnd)'}>}Qty)

Make sure Report_Date and Max(DayStart) are in same format

Re: Date range in Set Analysis

Wed, 01 Oct 2014 08:09:40 GMT

yes, but that will only get the maximum dayend and daystart. i need to get per row

Re: Date range in Set Analysis

anbu1984 — Wed, 01 Oct 2014 08:15:39 GMT

Do you have that range in a variable or you calculate it?

Re: Date range in Set Analysis

Wed, 01 Oct 2014 08:17:13 GMT

It is calculated. Report Date - 6 days

Re: Date range in Set Analysis

jyothish8807 — Wed, 01 Oct 2014 08:29:16 GMT

Hi Lyn,

Do have a field which have Dtae=14 Sep 2014 and a Quentity associated with it?

Over gere i guess max(start date) is a calculated field.

Regards

Re: Date range in Set Analysis

ashfaq_haseeb — Wed, 01 Oct 2014 08:32:59 GMT

Then try like this

=Sum({<Report_Date={'>=$(=date(Max(DayStart)-6))<=$(=Max(DayEnd))'}>}Qty)

Regards

ASHFAQ

Re: Date range in Set Analysis

Wed, 01 Oct 2014 08:33:24 GMT

Hi, Report date is associated with the Qty. Yes, for example I have to find the quantity between the dates given above.

If report date is 20-Sep-14 then the date range should be from what is above.

Re: Date range in Set Analysis

Wed, 01 Oct 2014 08:49:03 GMT

Hi doing this will only take the maximum of all..

so max(daystart) will not compute per row but will compute the total.. so max(daystart) in that set analysis will output = 19-sep-14

Re: Date range in Set Analysis

PrashantSangle — Wed, 01 Oct 2014 09:22:20 GMT

Hi,

then try your expression with if clause

Regards

Re: Date range in Set Analysis

anbu1984 — Wed, 01 Oct 2014 09:24:20 GMT

=Sum(If(Num(Report_Date) >= (Report_Date-6) And Report_Date<=Report_Date,Qty))

Re: Date range in Set Analysis

Wed, 01 Oct 2014 09:55:50 GMT

here's a sample document you guys can check out

Re: Date range in Set Analysis

Wed, 01 Oct 2014 10:44:29 GMT

jagan mohan

Re: Date range in Set Analysis

Wed, 01 Oct 2014 12:28:07 GMT

I have attached a sample qvw below

Re: Date range in Set Analysis

Wed, 01 Oct 2014 16:09:35 GMT

bump

Re: Date range in Set Analysis

jagan — Tue, 07 Oct 2014 04:58:13 GMT

Hi,

Qlikview calculations are done at table level not by row level, so it is not possible in front end, if you just want for one day then use below expression

=Sum({<Report_Date={'>=$(=date(Max([Rpt Date])-6))<=$(=Date(Max([Rpt Date])))'}>}Qty)

if you want for all the days then you have pre calculate in script by using Peek or Previous().

Hope this helps you.

Regards,

Jagan.