Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Sunny,
works perfectly. Thank you so much!
Best regards
Frank