Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
We have data that we download that is already in Rolling 12 form - from a system with a code-base of 1977
MY PROBLEM:
We want to analyze inventory in point in time slices.
If my Ending Date is 10/4/16 then rolling 12 Beginning Date is 10/5/15
If there is data on 10/22 then the year is 2015
If there is data on 10/3 then the year is 2016
I need a method to add the year to my transaction rows and am struggling to think through how to do this.
Is this what you are looking for?
Roller:
LOAD *,
If(Match(TypeSort,'BEG','END'), Left(TransactionDate,2)&'/'&Mid(TransactionDate,3,2)&'/20'&Right(TransactionDate,2),
MonthNo & '/'&Day & '/' & If(MakeDate(Year(Today()), MonthNo, Day) <= Today(1), Year(Today()), Year(Today())-1)) as TranDate
Resident R12Temp;
Are you only looking to add year to your date field? I think it would be very helpful if you can share some sample data to understand what you have and what you are looking to get
Hi Cam,
Create variables for Ending Date and Beginning date.
And create an expression in the chart object like below with MonthName(Datefield) as dimension:
Sum({$<DateField={">=$(=Date($(vBeginDate),'MM/DD/YY')) <=$(=Date($(vEndDate),'MM/DD/YY'))"}>}[Volume Total]).
It will automatically display rolling 12 months data based on the dates provided in the Set analysis variables.
Sorry - had to do some scrubbing
Please advise
Sorry - had to scrub and it was late - see attached below
Yes - the goal is to get to this point. See attached example
Is this what you are looking for?
Roller:
LOAD *,
If(Match(TypeSort,'BEG','END'), Left(TransactionDate,2)&'/'&Mid(TransactionDate,3,2)&'/20'&Right(TransactionDate,2),
MonthNo & '/'&Day & '/' & If(MakeDate(Year(Today()), MonthNo, Day) <= Today(1), Year(Today()), Year(Today())-1)) as TranDate
Resident R12Temp;
I changed to your formula and it works perfectly except a minor tweek.
Our system is a batch system, so Today() is really 2015 in the Rolling 12. Today won't be 10/5 until we run close this evening.
New file
So instead of less than equal to, you might have just used less than Today(1), right?