topic How to Seperate Amount between two given dates and sharing the amount equally between qtrs in App Development

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

Kumar_Reddy — Thu, 08 Oct 2020 20:40:00 GMT

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!

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

Taoufiq_Zarra — Fri, 09 Oct 2020 08:14:22 GMT

@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:

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

Kumar_Reddy — Fri, 09 Oct 2020 13:23:21 GMT

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

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

Taoufiq_Zarra — Fri, 09 Oct 2020 13:26:30 GMT

@Kumar_Reddy can you share the expceted (vizulisation)?