Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Addmonths in script

Hi,

I need to create transactions for a time period in a load script where I adding months within a time interval.

For example an action will happen every fourth month within the period 1.1.2014-30.6.2015 then I will need transactions with the following dates:

1.1.2014

1.5.2014

1.9.2014

1.1.2015

1.5.2015

Thank's

1 Solution

Accepted Solutions
MVP
MVP

Re: Addmonths in script

Not sure if this is what you are looking for but this script will generate transactions for you within a timespan and one event for every 4 months. You can introduce a random factor to filter out events too:

//Function to calculate Months Difference in script

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

vStart = MakeDate( 2014, 1 , 1);

vEnd = MakeDate( 2015, 6 , 30 );

Months = Ceil( $(MonthDiff(vStart,vEnd)) / 4 );

TRANSACTIONS:

LOAD

  *

WHERE

  Rand()<0.5;   // Filter out randomly 50% of the events - if you want ALL set it to <1

LOAD

  TransID & '-' & IterNo() AS TransID,

  Date,

  'Event #' & IterNo() AS EventID

WHILE

  IterNo() < 10;   // Generate 9 events for each month that comes from below

LOAD

  RecNo() AS TransID,

  AddMonths( '$(vStart)' , (RecNo()-1)*4 ) AS Date

AUTOGENERATE

  $(Months);

7 Replies
MVP
MVP

Re: Addmonths in script

Do you need to create rows with transactions?

bunnyv123
Contributor

Re: Addmonths in script

Hi,

By using your date field ,

you can create a flag in Script,

If(Date>= AddMonths(Today(), -3), 1)as Last3MonthFlag

and in the front end you call it like,

Last3MonthFlag={1}


for 3 months data in chart to be displayed.



I hope this helps you .



Thanks,

Bunny

Not applicable

Re: Addmonths in script

yes  I need to create transaction to my fact table

Not applicable

Re: Addmonths in script

Thank's,
But I think this will not help since I need to calculate the dates for occurrence

bunnyv123
Contributor

Re: Addmonths in script

Dear peter ,

Please Help me in obtaining this Its working on my local System but not on Test Instance.

Pivot Table Current and Previous Using above

Thanks in Advance.

Bunny

MVP
MVP

Re: Addmonths in script

Not sure if this is what you are looking for but this script will generate transactions for you within a timespan and one event for every 4 months. You can introduce a random factor to filter out events too:

//Function to calculate Months Difference in script

SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);

vStart = MakeDate( 2014, 1 , 1);

vEnd = MakeDate( 2015, 6 , 30 );

Months = Ceil( $(MonthDiff(vStart,vEnd)) / 4 );

TRANSACTIONS:

LOAD

  *

WHERE

  Rand()<0.5;   // Filter out randomly 50% of the events - if you want ALL set it to <1

LOAD

  TransID & '-' & IterNo() AS TransID,

  Date,

  'Event #' & IterNo() AS EventID

WHILE

  IterNo() < 10;   // Generate 9 events for each month that comes from below

LOAD

  RecNo() AS TransID,

  AddMonths( '$(vStart)' , (RecNo()-1)*4 ) AS Date

AUTOGENERATE

  $(Months);

Not applicable

Re: Addmonths in script

Thank you Petter

This solution will work :-)

Community Browser