Filter data such as 1 Week, 2 Week and 30 days

[kirpal singh manral]

Hi,

I am trying to create a calculated column based on date difference comparing two dates but not getting the desired output.

I am using Date Diff function to get days(Column Name: DateDiff) and converting it to integer. Sample values are 0,1,2,3,4,8,9,10,13,87,14,17,19,89,65,55,23,30.

Then using the below expression but not getting the desired output.

-------------------------------------------------------

Expression used

-----------------------------------------------------

case

when [DateDiff] 1 Month

--------------------------------------------------

Desired output:

0,1,2,3,4 => 1 Week

0,1,2,3,48,9,10,13,14 =>2 Week

0,1,2,3,48,9,10,13,14,17,19,23,30 => 1 Month

 

Thanks in Advance.

[Kirsten Smith (she/her)]

In your 'Desired output' example you are saying that values of 0-4 would have three different values, but Spotfire can only put one value in your new column.   It seems as though you may need to re-think the actual output you are looking for.

[Shashank Gurnalkar]

You might need to manipulate the resulting column of String type. You can use the following expression (or of similar type) and see if it helps.

case

 

when [Diff]

[kirpal singh manral]

Thanks Kirsten. I got your point.

 

Actually the end result i want is to filter data based on 1 week, 2 week and 3 weeks from current date . I want to button for each time period or just a filter.

 

 

[kirpal singh manral]

Thanks Shashank. It gives me the same output.

 

Actually the end result i want is to filter data based on 1 week, 2 week and 3 weeks from current date . I want to do it by a button for each time period or just a filter.

[Tyger Guzman 2]

I would suggest using Limting Expression for dates :

https://spotfired.blogspot.com/2014/09/last-week-month-date-expressions.html

Use these or similar expresssion in a Drop Down Document Property and have your visuals Data Limting Expression set by that document property.

 

 

 

 

This Week

Week([Date]) = week(DateTimeNow()) and Year([Date]) = year(DateTimeNow())

 

 

This Week and Last Week

Week([Date]) >= week(DateTimeNow())-1

and Week([Date]) = week(DateTimeNow())-2

and Week([Date])

[kirpal singh manral]

Thanks @tyger0951. It really helped.