topic Re: Date range in set analysis in QlikView

Date range in set analysis

Mon, 21 Dec 2015 16:26:08 GMT

I am having an issue with date range in set analysis. I have checked many an examples on this site but none seem to work.

What I am doing is select a date via calendar tool, set the variable vTestDate, then I want to see the count of dates or values for each date change on the basis of selection. I have attached a qvw.

Data is like

Load ts, group, alt, value Inline

[

ts, group, alt, value
2/12/2015,A,x,1.1
2/13/2015,A,x,1.2
2/14/2015,A,x,1.3
2/15/2015,A,x,1.4
2/16/2015,A,x,1.5
2/17/2015,A,x,1.6
2/18/2015,A,x,1.7
2/12/2015,B,y,1.8
2/13/2015,B,y,1.9
2/14/2015,B,y,2
2/16/2015,B,y,2.2
2/17/2015,B,y,2.3
2/18/2015,B,y,2.4
2/12/2015,C,z,2.5
2/13/2015,C,z,2.6
2/14/2015,C,z,2.7
2/17/2015,C,z,2.8
2/18/2015,C,z,2.9

];

Then create pivot table with 3 expressions

1. count(value)

2. count({$<group={A,B,C} >} value)

3. count({$<ts={'<$(vTestDate)-1'} >} value)

Expression 1 and 2 produce right results.

However, the expression 3 has two issues

a. it does not provide the correct count based on selected vTestDate. I tried using today() also, not much luck.

b. it does not respond to change in subtraction, be it -1 or -10

Thanks for your prompt reply

Varum

Message was edited by: Varum Reddy correct the column header for the table

Re: Date range in set analysis

Mon, 21 Dec 2015 18:58:32 GMT

Hi,

Try this

count({$<ts={'<$(date(vTestDate)-1)'} >} value)

Re: Date range in set analysis

sunny_talwar — Mon, 21 Dec 2015 18:58:40 GMT

May be this:

=Count({$<ts={"$(=Date(vTestDate-1))"}>} value)

Re: Date range in set analysis

Mon, 21 Dec 2015 20:12:12 GMT

Does not work if I want set of dates less than vTestDate - gives 0 count

for example,

=count({$<ts={'$(<Date(vTestDate))'}>} value)

Re: Date range in set analysis

Mon, 21 Dec 2015 20:14:01 GMT

Count in the set does not change with vTestDate or the subtraction - always the total of the unfiltered set.

Re: Date range in set analysis

Mon, 21 Dec 2015 20:52:10 GMT

Ideally, I need it to work for the date range of vTestDate-2 to vTestDate. In reality, 2 could be 30, 60 or 90 on the actual dataset.

I had tried for days various date range suggestions on this community, but none produced expected results. That is the reason I created the qvw for you to see the results too.

Re: Date range in set analysis

Mon, 21 Dec 2015 21:32:23 GMT

Try this

count({$<ts={'<$(=date(vTestDate-1))'} >} value)

Re: Date range in set analysis

swuehl — Mon, 21 Dec 2015 21:47:21 GMT

=count({$<ts={">=$(=Date(vTestDate-2))<=$(=Date(vTestDate))"} >} value)

and change the calendar object constraints so that it allows a proper variable selection

Re: Date range in set analysis

Mon, 21 Dec 2015 22:03:43 GMT

Both of the following syntax work.

count({$<ts={'>$(=date(vTestDate-2))<=$(=date(vTestDate)) '} >} value)
count({$<ts={'>$(=vTestDate-3)<=$(=vTestDate) '} >} value)
I am curious why is the dollar-sign expansion necessary?

Thanks.

Re: Date range in set analysis

swuehl — Mon, 21 Dec 2015 22:29:58 GMT

Have a look at

A Primer on Set Analysis

Re: Date range in set analysis

sunny_talwar — Tue, 22 Dec 2015 01:44:44 GMT

Varum I am going to share a method I use whenever it comes to dates. I have seen a lot of people struggling when it comes to date and but this method has never let me down.

Whenever I want to use dates within set analysis, I usually create a text box with the range I want to cover in my set analysis. For your case, lets say you want between vTestDate-2 and vTestDate. I would create a text box like this:

='>=' & Date(vTestDate -2) & '<=' & Date(vTestDate)

This should show the date range you want and in the format if your date field.

and bingo it does. Next step is fairly simple;

Copy the whole text box expression and paste it between the parenthesis->

{<DateField = {"$()"}>} -> {<DateField = {"$(='>=' & Date(vTestDate -2) & '<=' & Date(vTestDate))"}>} and you should be all set.

Expression -> =Count({$<ts={"$(='>=' & Date(vTestDate-2) & '<=' & Date(vTestDate))"}>} value)

I hope after today you will probably NOT need to worry about using date range in your set analysis ever again

Re: Date range in set analysis

jagan — Tue, 22 Dec 2015 01:55:12 GMT

Hi,

Check this link for lot of examples similar to this

Set Analysis for certain Point in Time

Re: Date range in set analysis

Tue, 22 Dec 2015 20:26:32 GMT

When I mix this date range, at least, with a negation (-=) filter criteria, in the qvw, I attached, QV does not like it in the expression box. But it seems to produce correct results. I am wondering if it is okay to use this or is there a better way to use " not in " clause in the set.

Re: Date range in set analysis

sunny_talwar — Tue, 22 Dec 2015 21:22:20 GMT

What you are using is not wrong. I am guessing you are using QV 11.20 SR 8 or below? This is just suggesting that QlikView's Expression Editor doesn't understand the script, but there is nothing wrong with the expression itself. If you update to newer versions, you won't see those red wiggly lines for using -= in set analysis.

Re: Date range in set analysis

usa_slin — Tue, 25 Feb 2020 20:17:29 GMT

Thank you so much! I've been looking for the solution for 4 hr+😝

I had a similar but different case and I'd like to share:

I want to filter data by a user defined date range.

This code works for me:

Sum( { $ < LINE_CREATED_DATE = { ">=$(=Date(Start_date))<=$(=Date(End_date))" } >} cargo_volume)

Start_date and End_date are VARIABLES which take date entry from users.

(Variable video: https://www.youtube.com/watch?v=uFT-PNf_z9g)

Date() turns date format into "mm/dd/yyyy"

After the LINE_CREATED_DATE column was converted into "mm/dd/yyyy" format and the magic code works!