How to Seperate Amount between two given dates and sharing the amount equally between qtrs

[Kumar_Reddy]

Hi All,

I am new to QS and looking for some suggestions here.

I have a requirement a start date and end date fields and i need to generate quarters between these two dates and divide the amount equally between these quarters. 

ID	Amount	Start	End
1	5000	1/1/2019	2/1/2023
2	10000	1/8/2020	5/3/2025
3	3000	7/8/2020	8/8/2024
4	6000	8/6/2021	8/6/2023
5	5000	10/1/2019	10/1/2023

I want to First Generate Quarters between these two dates. After generating quarters i want to sum up the each Qtr share for these 5 IDs and want to know how much i will be paying out each qtr. (like  forecasting. )

Also Please help me on how to show this in a line chart

Thank you all for your help! 

[Taoufiq_Zarra]

@Kumar_Reddy  like ?

QuartersMap:

MAPPING LOAD 

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Data:

LOAD ID, Amount, Date#(Start,'D/M/YYYY') as Start, Date#(End,'D/M/YYYY')  as End INLINE [
    ID, Amount, Start, End
    1, 5000, 1/1/2019, 2/1/2023
    2, 10000, 1/8/2020, 5/3/2025
    3, 3000, 7/8/2020, 8/8/2024
    4, 6000, 8/6/2021, 8/6/2023
    5, 5000, 10/1/2019, 10/1/2023
];
left join load  ID, Amount,ApplyMap('QuartersMap', month(Date(Start + IterNo() - 1)), Null())&'_'&Year(Date(Start + IterNo() - 1)) as Quarter

 resident Data
While (Start + IterNo() -1) <= End;


Tmp:
noconcatenate

load distinct * resident Data;

drop table Data;

Final:
noconcatenate

load  * resident Tmp;
left join 

load   ID,Start, End,count(Quarter) as countQuarter resident Tmp group by ID,Start, End;

drop table Tmp;

and in chart

Dimention ID,Start,ENd

Expression:

Max(Amount)---> for Total Amoun

Max(Amount)/countQuarter ---> for Amount by Quarter

output:

[Kumar_Reddy]

Hi @Taoufiq_Zarra ,

Thank you for your help!

I need the Quarter to be shown up as header linke Q3 2019,Q4 2019,Q1 2020,Q2 2020 and below the amounts. I think we need to do this using pivot table so that the amounts can be seperated by each qtr. But i need to total payout of each qtr. Because each qtr payout will be different as they start at different dates

Thanks,

Kumar

[Taoufiq_Zarra]

@Kumar_Reddy  can you share the expceted (vizulisation)?