Date range in set analysis

[#1]

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

[sunny_talwar]

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

[jagan]

Hi,

Check this link for lot of examples similar to this

Set Analysis for certain Point in Time

[#4]

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.

[sunny_talwar]

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.

[usa_slin]

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!