If condition is incorrect in script - problem adding x amount of months to a date

[Anonymous]

Hello Qlik Community,

I have the following script that divides a yearly labor cost evenly into months based on a starting date. The script works great as it assumes the yearly cost should be split from month([Start Date]) through the end of the year. In other words, monthly cost = yearlyCost / (13 - month([Start Date])). I have several cases where the length of the contract does not continue through the end of the year. There is another field in the data that is the length of the contract which can be used to solve this problem. Below is the code of the working script. I am using Qlik Sense in the cloud.

table1:
LOAD Name,
Date([Start Date]) as [Start Date],
Money(yearlyCost) as [yearlyCost],
Month(Date#(IterNo(),'M')) as Month,
Money(If(IterNo()>=Month([Start Date]),[yearlyCost]/(13-Month([Start Date])),0)) as [Direct Labor]
FROM mytable.xlsx (ooxml, embedded labels, table is Sheet1)  While IterNo()<=12;

I have changed my if statement in line 6 to the following, which utilizes the field numMonths.

Money(If(IterNo()>=Month([Start Date]) and '$(Month)' <= Month([StartDate]) + Month(Date#([numMonths], 'M')),[2019 NTC Direct Labor]/numMonths,0)) as [Direct Labor]

This is what the output should look like below except for in the case of name = b, the numMonths = 2 so only June and July should split the total cost at $2500 each. Same with name = c, spread out over 4 months (May, June, July, August).

Any help on getting this working correctly?

Thanks,

Zach

[sasiparupudi1]

pl attach sample excel with expected output

[Anonymous]

I have updated my question. Also, this is the link to the original question, maybe it provides more details.

Load in for each loop that iterates over months

Thanks,

Zach

[MarcoWedel]

Hi,

one possible solution using your sample data:

table1:
LOAD Name,
    Date([Start Date]) as [Start Date],
    Money([Yearly Direct Labor]) as [Yearly Direct Labor],
    Month(Date#(IterNo(),'M')) as Month,
    numMonths,
    Money(If(IterNo()>=Month([Start Date]) and IterNo()<Month([Start Date])+numMonths,[Yearly Direct Labor]/numMonths,0)) as [Direct Labor]
FROM [https://community.qlik.com/servlet/JiveServlet/download/1463901-320647/Example.xlsx] (ooxml, embedded labels, table is Sheet1)
While IterNo()<=12;

What result do you expect for Start Date / numMonths combinations that span multiple years?

hope this helps

regards

Marco