really stuck.. distribute a contract volume over the months between start & end date

[suturb]

Hi all, 

i'm really stuck on a probably stupid question: 

I have an overview of contracts, with contract volumes, and a specified start date and end date for each of these contracts. 

I want to plot my contract volumes against my actual sales, however, on a monthly basis. Therefore I need to distribute my contract volumes evenly over the months between start and end date. 

I assume this can be done by generating a table within the script, but I absolutely have no idea how. 

can somebody help please? 

[otmane]

Hi  suturb,

Correct, you have to add a chunk of script to your data model to association the sales to contracts, steps to folow :

- Contracts table : load * , contract_id&'_'&num(startdate) &'_'&num(enddate) as contract_interval resident contract

- Sales table : load *, sales_id&'_'&num(salesdate) as sales_date resident sales

- Create a temp bridge table using 

               IntervalMatch (salesdate,sale_id)
             Load distinct startdate, enddate, contract_id
             Resident Contracts table ;

- create a bridgetable :

              sale_id&'_'&num(salesdate) as sales_date,

             contract_id&'_'&num(startdate) &'_'&num(enddate) as contract_interval

             resident temp  bridge table 

- drop temp bridge table

- drop unused field

- left join BridgeTable with you data model

Regards,

Otmane

[suturb]

dear Otmane and others,  I'm really sorry but I actually do not have a programming background and i'm failing to make your suggested solution work.. 

The script (automatically generated by QS) for loading my table at this point is the following (... where irrelevant fields have been removed for sake of simplicity). 

This script loads a table of contract numbers, start month for each contract, end month for each contract, contract volume ( Frcst Qntt) and other features, from an excel table. 

I now want, in addition to this information, to assign a monthly contract volume (just the contract volume divided by the number of contract months) to the months (MMMM.YY) between start and end month of each contract. 

Should this be part of the load script below, or is this a different load script? i'm afraid I really need some handholding here.. if needed I can share more information; 

many thanks in advance for your kind support, joris 

[DATA after filter ]:

LOAD

                [RM Contract],

                …

                Date(Date#([Start Mnth], 'YYYYMM') ,'YYYYMM') AS [Start Mnth],

                Date(Date#([End Month], 'YYYYMM') ,'YYYY.MM') AS [End Month],

                [Frcst Qntt],

                …,

                APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([Qty Unit]), '-') AS [DATA after filter .Qty Unit_GeoInfo]

 FROM [lib://LVG QS/ZMC1CNTRCT17 - Overview Contracts .xlsx]

(ooxml, embedded labels, table is [DATA after filter ]);

[otmane]

Hi suturb,

now all you need is to calculate the age of a contract in months and use it as denominator , hope this post helps you :

https://community.qlik.com/t5/QlikView-Documents/Calculating-Months-difference-between-two-dates/ta-...

Regards,

Otmane

[suturb]

dear, I tried to implement your suggestion but it does not work.. 

also: I would expect that somewhere in the script a table would be generated with following columns: 

          Contract number / month / contract volume divided by number of months. 

a contract with a duration of 12 months, should then result in 12 lines of this table; does that make sense? 

how do I get to such a solution?

joris 

[suturb]

hi all,

anyone else who could help me with above question?

kind regards,

joris