Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Kumar_Reddy
Contributor
Contributor

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

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. 

IDAmountStartEnd
150001/1/20192/1/2023
2100001/8/20205/3/2025
330007/8/20208/8/2024
460008/6/20218/6/2023
5500010/1/201910/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! 

3 Replies
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:

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kumar_Reddy
Contributor
Contributor
Author

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)?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉