Converting volume from a table containing start and end dates to a yearly/monthly form

[FrankK]

Hi everyone,

I have the following data, which I want to convert in order to be able to perform better analysis in Qlik Sense:

Project_ID;Start_Date;End_Date; Volume
1;01.01.2018;31.12.2019; 120.000,00 €  // Project runs for 12 months: 10.000 Euros per month
2;01.03.2019;15.06.2019; 48.000,00 €  // Project runs for 4 months: 12.000 Euros per month
3;01.06.2019;31.10.2019; 200.000,00 € // Project runs for 5 months: 40.000 Euros per month

The resulting table should be in the following form:

Year;Month;; Volume
2019;1;; 10.000,00 €
2019;2;; 10.000,00 €
2019;3;; 22.000,00 € // Volume from Projects 1 (10.000 €) and 2 (12.000 €)
2019;4;; 22.000,00 €
2019;5;; 22.000,00 €
2019;6;; 62.000,00 €
2019;7;; 50.000,00 €
2019;8;; 50.000,00 €
2019;9;; 50.000,00 €
2019;10;; 50.000,00 €
2019;11;; 10.000,00 €
2019;12;; 10.000,00 €

Thanks in advance for any help!

Best regards,
Frank

[sunny_talwar · Accepted solution]

Try this

Table:
LOAD *,
	 Year(Month) as Year;
LOAD *,
	 Volume/((Month(End_Date)+Year(End_Date)*12) - (Month(Start_Date)+Year(Start_Date)*12)+1) as VolumePerMonth,
	 Date(MonthStart(Start_Date, IterNo() - 1), 'YYYYMM') as Month
While MonthStart(Start_Date, IterNo() - 1) <= End_Date;
LOAD * INLINE [
    Project_ID, Start_Date, End_Date, Volume
    1, 01/01/2019, 12/31/2019, 120000
    2, 03/01/2019, 06/15/2019, 48000
    3, 06/01/2019, 10/31/2019, 200000
];

Now all you need is Sum(VolumePerMonth) with Month as your dimension

[FrankK]

Hi Sunny,

works perfectly. Thank you so much!

Best regards
Frank