Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date range in set analysis

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

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

View solution in original post

14 Replies
Not applicable
Author

Hi,

Try this

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

sunny_talwar

May be this:

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

Not applicable
Author

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

for example,

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

Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

Try this

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

swuehl
MVP
MVP

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

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

Not applicable
Author

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.

swuehl
MVP
MVP

Have a look at

A Primer on Set Analysis