topic Re: Dynamic Date in Set Analysis in QlikView

Dynamic Date in Set Analysis

Tue, 09 Aug 2016 01:26:56 GMT

Hi All,

I trying to get total number of orders completed in last 4 weeks... I am using following expression

Count({$<[Order Status]={'Complete'}, [Product Category]={'Shoes'},[Completion Date]}>}OrderId)

where can I add that last 4 weeks clause in the above set analysis ?

Completion Date field name is [Completion Date]

Re: Dynamic Date in Set Analysis

sunny_talwar — Tue, 09 Aug 2016 02:19:10 GMT

May be like this:

Count({$<[Order Status] = {'Complete'}, [Product Category] = {'Shoes'}, [Completion Date] = {"$(='>=' & Date(Max([Completion Date]) - 28, 'DateFieldFormatHere') & '<=' & Date(Max([Completion Date]), 'DateFieldFormatHere'))"}>}OrderId)

Re: Dynamic Date in Set Analysis

marjan_it — Tue, 09 Aug 2016 06:04:01 GMT

you can use it:

=Count(if(Order Status='Complete' and Product Category='Shoes' and (Num(Week(date(now()))) - Num(Week(SubmitDocDate1))=4),OrderId))

Re: Dynamic Date in Set Analysis

Tue, 09 Aug 2016 06:21:22 GMT

Hi Lokesh:

You can try this :

=Count(if(Order Status='Complete' and Product Category='Shoes' and (Num(Week(date(now()))) - Num(Week(SubmitDocDate1))=4),OrderId))

Thanks

Nandu

Re: Dynamic Date in Set Analysis

jyothish8807 — Tue, 09 Aug 2016 06:27:01 GMT

Hi Sunny,

Just a bit curious to know why are you using ' ' and & while using >= in set analysis ?is there any particular reason you are using this ?

{"$(='>=' & Date(Max([Completion Date]) - 28, 'DateFieldFormatHere')

Thanks a lot in advance. I have learned a lot from your solutions

Regards

Re: Dynamic Date in Set Analysis

Anonymous — Tue, 09 Aug 2016 07:56:32 GMT

Hi Nanda, try this:

Count({$<[Order Status] = {'Complete'}, [Product Category] = {'Shoes'},

[Completion Date] = {$(>=Date(MakeWeekDate(Year(WeekStart(Max([Completion Date])-28)), Week(WeekStart(Max([Completion Date])-28))),'DD/MM/YYYY'))}>}OrderId)

It will get first day of 4 weeks before.

Regards!!!

Re: Dynamic Date in Set Analysis

Mark_Little — Tue, 09 Aug 2016 08:02:19 GMT

Hi,

Things like this i would normally do in script as it helps with front end performance.

in you calendar add

IF(Date>= Today()-28 and Date<=Today(),1,0) As RollingMonth

Then you set analysis would be

Count({$<[Order Status]={'Complete'}, [Product Category]={'Shoes'},RollingMonth={1}}>}OrderId)

Mark

Re: Dynamic Date in Set Analysis

sunny_talwar — Tue, 09 Aug 2016 08:05:12 GMT

But mark, how would this be dynamic? Once set, flags will be fixed to 0 and 1 and won't change based on selections. Isn't it?

Re: Dynamic Date in Set Analysis

Mark_Little — Tue, 09 Aug 2016 08:08:30 GMT

Hi Sunny,

I guess it depends how he means, I took it to mean the prior 28 days, so on a nightly reload this would always show the last 28 days.

If he is wanting the 4 weeks prior to a selection then you're right, my approach is terrible.

Mark

Re: Dynamic Date in Set Analysis

Tue, 09 Aug 2016 08:08:33 GMT

Thank you all for reply..

I have temporarily used if statement in my expression instead of set analysis.

Count(Distinct(if([Order Status] = 'Complete' AND [Product Category] = 'Shoes' AND [Completion Date] > Date(Today()-28), OrderId)))

I will try one of your Set Analysis Expression and mark the correct answer tomorrow

Thank you.

Re: Dynamic Date in Set Analysis

sunny_talwar — Tue, 09 Aug 2016 08:10:27 GMT

Hi Jyothish -

A lot of the people don't use this syntax, but I like using this because it gives me the flexibility to check my inner portion of the set analysis here. Basically, if you pick this (the portion in red)

[Completion Date] = {"$(='>=' & Date(Max([Completion Date]) - 28, 'DateFieldFormatHere') & '<=' & Date(Max([Completion Date]), 'DateFieldFormatHere'))"}

and paste it in a text box object, you will see the date range needed and in the format needed. If either of the two things don't match, you have to fix it and you can fix that in the text box object. Once fixed, copy the complete expression and paste it between the dollar sign expansion

Alternative to the above being used is this:

[Completion Date] = {">= $(=Date(Max([Completion Date]) - 28, 'DateFieldFormatHere'))<= $(=Date(Max([Completion Date]), 'DateFieldFormatHere'))"}

Which works equally well.

HTH

Best,

Sunny

Re: Dynamic Date in Set Analysis

sunny_talwar — Tue, 09 Aug 2016 08:11:25 GMT

I guess you are right, from his response below (which used today), your script method seems to make sense

Re: Dynamic Date in Set Analysis

jyothish8807 — Thu, 11 Aug 2016 06:20:44 GMT

Thanks for the explanation

Br,

Re: Dynamic Date in Set Analysis

sunny_talwar — Thu, 11 Aug 2016 09:09:11 GMT

No problem at all