Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
antoniodneto
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
UncleRiotous
Creator
Creator

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())

antoniodneto
Creator II
Creator II
Author

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!

antoniodneto
Creator II
Creator II
Author

@sunny_talwar @Channa any tips? Tks!

Channa
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
antoniodneto
Creator II
Creator II
Author

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.

Channa
Specialist III
Specialist III

what is ur data source SQL or oracle

we will try to build view

Channa
antoniodneto
Creator II
Creator II
Author

Hi @Channa is SQL.

antoniodneto
Creator II
Creator II
Author

Hi @Channa I replied your message, tks!

Channa
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