Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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

View solution in original post

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