QlikView Scripting

[#1]

I have the following Statement

Load

AcctNumber

AcctName

FiscalYearStartDate

BillStartDate

from

acct.qvd

Now I have 3 clients

Each one of them has a Different FiscalYearStartDate.  Lets use there examples

Client A - FiscalYearStartDate 7/1/2013

Client B - FiscalYearStartDate 8/1/2013

Client C - FiscalYearStartDate 9/1/2013

Now the clients enter bills with a BillStartDate each month.  Now lets use BillStartDate = 1/1/2014.

For Client A BillStartDate 1/1/2014 would be month 7 in a 12 month cycle because their FiscalYearStartDate was 7/1/2013.

For Client B BillStartDate 1/1/2014 would be month 6 in a 12 month cycle because their FiscalYearStartDate was 8/1/2013.

For Client C BillStartDate 1/1/2014 would be month 5 in a 12 month cycle because their FiscalYearStartDate was 9/1/2013.

So what I want to accomplish is to normalize the BillStartDate based on the FiscalYearStartDate.  Is this something that I can use Qlikview to accomplish?

David

[#2]

Hi David,

Perhaps you can use a flag in the fact table and the same flag in your calendar table. I guess that you are using two tables, bills table and calendar table and the relation is by the date field. If you can identify all possibles fiscal years and create a calendar for each fiscal year with a flag, and the bills table includes the flag too, then you can relation the tables by Date-Flag field.

Can you try it?

Tell me if you have any question.

Regards