Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

DIMENSION VALUE ONE YEAR AGO

Hi Guys,

Can you help me?

I have in my table DATE,STATUS.

I want to create a new dimension DATE_Y_AGO.

The result is something like IF DATE = MAX(DATE) then STATUS = MAX(DATE,12). I want to return how was the STATUS one year ago to compare.

Any ideas?

Tks a lot!

10 Replies
Highlighted
Contributor III
Contributor III

If I understand correctly then you should just be able to query the date 12 months ago using AddMonths.

Depending how you want to use it either

=AddMonths(Max(DATE),-12)

or

=if(DATE=AddMonths(Max(DATE),-12) ,STATUS,null())

Highlighted
Creator II
Creator II

Hi,

The expression =AddMonths(Max(DATE),-12) works to show me one year ago.

But I want to create a cross table like this

                          DATE 1 YEAR                     DATE

                                          201801 201802 201803 201804 201805

                         201801

                         201802

                         201803

 

DATE in columns and DATE year ago in lines. If I use the expression as a dimension returns me an error.

Tks!

Highlighted
Creator II
Creator II

@sunny_talwar @Channa any tips? Tks!

Highlighted
Specialist III
Specialist III

If i understand correct you like to show what is the status of each date 12 months back
If you select jan 1st 2018 you want to get status of jan 1 st 2017???
Channa
Highlighted
Creator II
Creator II

Exactly Channa!

 

For example I have a client in 1st Jan 2018 with STATUS = 1

In 2017 he was STATUS = 2 thats why I want do calc the variation between these two months.

Highlighted
Specialist III
Specialist III

what is ur data source SQL or oracle

we will try to build view

Channa
Highlighted
Creator II
Creator II

Hi @Channa is SQL.

Highlighted
Creator II
Creator II

Hi @Channa I replied your message, tks!

Highlighted
Specialist III
Specialist III

SELECT a.ARRDate,
a.SomeData, --CurrentYeardate
a.Status,CuryearStatus
b.SomeData --Previous
b.Ststus --PreviousYearstatus
FROM MyTable a
LEFT JOIN MyTable b
ON DATEADD(year,-1,a.ARRDate) = b.ARRDate

Channa