topic Re: Help adding information to Rolling 12 data in QlikView

Help adding information to Rolling 12 data

Wed, 05 Oct 2016 05:16:09 GMT

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

In the data we get a Beginning Balance with date and Ending balance with Date.
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.

Re: Help adding information to Rolling 12 data

sunny_talwar — Wed, 05 Oct 2016 10:05:52 GMT

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

Re: Help adding information to Rolling 12 data

Anonymous — Wed, 05 Oct 2016 10:27:39 GMT

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.

Re: Help adding information to Rolling 12 data

Wed, 05 Oct 2016 18:22:32 GMT

Sorry - had to do some scrubbing

Please advise

Re: Help adding information to Rolling 12 data

Wed, 05 Oct 2016 18:23:00 GMT

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

Re: Help adding information to Rolling 12 data

Wed, 05 Oct 2016 18:33:59 GMT

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

Re: Help adding information to Rolling 12 data

sunny_talwar — Wed, 05 Oct 2016 18:48:19 GMT

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;

Re: Help adding information to Rolling 12 data

Wed, 05 Oct 2016 21:07:02 GMT

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.

Re: Help adding information to Rolling 12 data

Wed, 05 Oct 2016 21:08:04 GMT

New file

Re: Help adding information to Rolling 12 data

sunny_talwar — Wed, 05 Oct 2016 21:08:45 GMT

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

Re: Help adding information to Rolling 12 data

sunny_talwar — Wed, 05 Oct 2016 21:10:33 GMT

This?

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;

Re: Help adding information to Rolling 12 data

Wed, 05 Oct 2016 21:24:11 GMT

Duh.....that would be too easy.... thanks !