Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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())
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!
@sunny_talwar @Channa any tips? Tks!
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.
what is ur data source SQL or oracle
we will try to build view
Hi @Channa is SQL.
Hi @Channa I replied your message, tks!
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