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: 
Not applicable

Help adding information to Rolling 12 data

We have data that we download that is already in Rolling 12 form - from a system with a code-base of 1977

  1. In the data we get a Beginning Balance with date and Ending balance with Date.
  2. All the inventory transactions in between beg/end just have Month and Day of transaction but not Year.

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.

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

11 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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.

Not applicable
Author

Sorry - had to do some scrubbing

Please advise

Not applicable
Author

Sorry - had to scrub and it was late - see attached below

Not applicable
Author

Yes - the goal is to get to this point.   See attached example

sunny_talwar

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;

Not applicable
Author

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.

Not applicable
Author

New file

sunny_talwar

So instead of less than equal to, you might have just used less than Today(1), right?