Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
=count({$<ts={">=$(=Date(vTestDate-2))<=$(=Date(vTestDate))"} >} value)
and change the calendar object constraints so that it allows a proper variable selection
Hi,
Try this
count({$<ts={'<$(date(vTestDate)-1)'} >} value)
May be this:
=Count({$<ts={"$(=Date(vTestDate-1))"}>} value)
Does not work if I want set of dates less than vTestDate - gives 0 count
for example,
=count({$<ts={'$(<Date(vTestDate))'}>} value)
Count in the set does not change with vTestDate or the subtraction - always the total of the unfiltered set.
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.
Try this
count({$<ts={'<$(=date(vTestDate-1))'} >} value)
=count({$<ts={">=$(=Date(vTestDate-2))<=$(=Date(vTestDate))"} >} value)
and change the calendar object constraints so that it allows a proper variable selection
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.
Have a look at