Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

datalakeyu
Valued Contributor

How to get the same date in previous year with Qlik sense

We could use function Today() to get the date of today. For example, today is 7/11/2016. We will get 7/11/2016 with function Today().

How could we get date 7/11/2015 with Qlik time function?

If we have another day of this year, for example, 2/25/2016.

How could we get 2/25/2015 (the same date in the previous year) with Qlik time function?

Thanks so much for your help!

6 Replies

Re: How to get the same date in previous year with Qlik sense

May be using AddYears() function:

=Date(AddYears(Today(), -1))

datalakeyu
Valued Contributor

Re: How to get the same date in previous year with Qlik sense

Dear Sunny , thanks so much for your help!

Could you please give me further help?

My question is like this:

I would like to compare myValue in a time series with accumulation, so I use the following as measure.

The default date range is from the middle of January this year to current date of this year.

    If(GetSelectedCount(myDate),

        RangeSum ( above ( Sum(myValue) , 0 , RowNo() ) ) ,

       

        RangeSum ( above ( Sum({< myDate *= {">1/16/2016" } >} myValue) , 0 , RowNo() ) )

   

     )

I would like to compare the accumulation myValue in the same date range of previous year, so I add another measure. It will be something as:

    If(GetSelectedCount(myDate),

        RangeSum ( above ( Sum(myValue) , 0 , RowNo() ) ) ,

       

        RangeSum ( above ( Sum({< myDate *= {">1/16/2015 < Date(AddYears(Today(), -1))" } >} myValue) , 0 , RowNo() ) )

   

     )

  

Firstly, is my above expression correct?

Secondly, how to get 1/16/2015 from 1/16/2016 with a time function? If I directly type 1/16/2015, it will be hard coding. I would like to be dynamic.

Thanks again!

Re: How to get the same date in previous year with Qlik sense

What is the reason to start with 1/16? I guess we can use AddYears() to go back 1 year, but how do you get 1/16 in the first place?

datalakeyu
Valued Contributor

Re: How to get the same date in previous year with Qlik sense

Dear Sunny , thanks so much for your help!

Actually, it is 1/3/2016 (January 3, 2016). I use 1/16/2016 in case there is confusion between March 1, 2016. Sorry.

I would like to compare myValue in accumulation from the first week date of this year to current date to the same date range of previous year.

There is problem of my following expression:

    If(GetSelectedCount(myDate),

        RangeSum ( above ( Sum(myValue) , 0 , RowNo() ) ) ,

        RangeSum ( above ( Sum({< myDate *= {">1/16/2015 < Date(AddYears(Today(), -1))" } >} myValue) , 0 , RowNo() ) )

    )

Because in chart, plot of 2015 is before 2016 in x-axis.

Could you please give any suggestion?

Thanks!

Re: How to get the same date in previous year with Qlik sense

I guess you have myDate as your dimension? You will need to use Above() function to bring 2015 values in front of 2016 values but I would suggest an alternative to use The As-Of Table to get this done which will make your life so much easier with regards to selections etc...

datalakeyu
Valued Contributor

Re: How to get the same date in previous year with Qlik sense

Thanks! Yes, myDate is the chart dimension. I set a default date range, and I also use a calendar (myDate is also dimension of calendar) to pick other date range, so I use GetSelectedCount(myDate).

Community Browser