Time Intelligence Related Question

Question & Answer
, ,
1

Hi Experts,

How can I achieve this in Quickisight

select * From Table1
where date::date between convert_timezone(‘utc’, ‘us/pacific’, {{Start_Date}})-91
AND convert_timezone(‘utc’, ‘us/pacific’, {{End_Date}})-91

Logic/Context: I need to get Sales data between 2 dates & I need some specific date range that is data should be from 91 previous days of Start date & 91 previous days of end date .

Kindly help me
Thanks in Advance

2

Hello @ArnaJi88 !

Do you want to do this on your dataset level or do you want to do this in the analysis?

If you want to do it on your dataset level you could try using the Custom SQL depending on your datasource:

If you would rather do this on the analysis layer you could try using a calculated field:

Let me know if this is what you were looking for!

3

Hi Duncan,
Thank you for your reply & inputs
I need this in Analysis level & I tried this but no luck
Thanks

4

Hello @ArnaJi88 !

Have you tried something like this in your analysis?

addDateTime(-91, 'DD', ${StartDate})

5

Thank you for your reply,

I tried this & Created same for StartDate & EndDate, But i am not getting how to use these created fields to restrict/select the data range as above mentioned

Thanks

6

Hello @ArnaJi88 !

I’m sorry about the late reply. Could you try something like the following?

sumIf(
     {Sales}, 
     {Order Date} >= addDateTime(-91, 'DD', ${StartDate}) OR 
     {Order Date} <= addDateTime(-91, 'DD', ${EndDate})
   )

Please let me know if this helps!

2 Likes
7

Hi @duncan,

I have achieved this one with little different approach ,
I wanted this one for dynamic selection of previous days, so here is answer steps wise

Step – 1 ) We need to create 3 parameters here.

1.Start Date Parameter - Date parameter - ${StartDate}

2.End Date Parameter – Date parameter - ${EndDate}

3.Date Interval Parameter – Integer - ${SelectDays}

Step – 2 ) Calculations:

Start Days with X Days = addDateTime( - ${SelectDays}, ‘DD’, ${StartDate} )

End Days with X Days = addDateTime( - ${SelectDays}, ‘DD’, ${EndDate} )

Date Filter For X Days =

ifelse(

date_column >= {Start Days with X Days} AND date_column <= {End Days with X Days}, ‘Yes’, ‘No’)

Step – 3 )

Add Date Filter For X Days to Filter & select ‘Yes’

You can get dynamic previous days selection through ${SelectDays} Parameter & select the date range from ${StartDate} & ${EndDate}.

1 Like