Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
We have data that currently exist in this format:
S.No | Start Date | End Date |
1 | 15-Jan-2020 | 20-Feb-2020 |
2 | 10-Jan-2020 | 25-Mar-2020 |
3 | 15-Feb-2020 | 20-Jun-2020 |
My user is interested in looking at Number of Days between Start and End Date by Month for each Year. How can we achieve the below output using formulas or any other approch:
S.No | Start Date | End Date | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 |
1 | 15-Jan-2020 | 20-Feb-2020 | 16 | 20 | ||||
2 | 10-Jan-2020 | 25-Mar-2020 | 21 | 29 | 25 | |||
3 | 15-Feb-2020 | 20-Jun-2020 | 14 | 31 | 30 | 31 | 20 |
Thanks
Sushant
@imsushantjain try below
Data:
LOAD [S.No],
[Start Date],
[End Date],
date([Start Date]+IterNo()-1) as Date ,
date(monthstart([Start Date]+IterNo()-1),'MMM YYYY') as Month,
RowNo()&num([Start Date]+IterNo()-1) as Key
FROM table
while [Start Date]+IterNo()-1<=[End Date];
Below is the pivot table view with expression count(distinct Date)
If you don't want the Day level data in table the you can aggregate it to month level and calculate days in load script to reduce the number of records. Script will look something like below
Data:
LOAD [S.No],
[Start Date],
[End Date],
date([Start Date]+IterNo()-1) as Date ,
date(monthstart([Start Date]+IterNo()-1),'MMM YYYY') as Month
FROM table
while [Start Date]+IterNo()-1<=[End Date];
Final:
load [S.No],
[Start Date],
[End Date],
Month,
count(distinct Date) as Days
resident Data
group by [S.No],
[Start Date],
[End Date],
Month;
Now you can simply use sum(Days) in your expression
@imsushantjain try below
Data:
LOAD [S.No],
[Start Date],
[End Date],
date([Start Date]+IterNo()-1) as Date ,
date(monthstart([Start Date]+IterNo()-1),'MMM YYYY') as Month,
RowNo()&num([Start Date]+IterNo()-1) as Key
FROM table
while [Start Date]+IterNo()-1<=[End Date];
Below is the pivot table view with expression count(distinct Date)
If you don't want the Day level data in table the you can aggregate it to month level and calculate days in load script to reduce the number of records. Script will look something like below
Data:
LOAD [S.No],
[Start Date],
[End Date],
date([Start Date]+IterNo()-1) as Date ,
date(monthstart([Start Date]+IterNo()-1),'MMM YYYY') as Month
FROM table
while [Start Date]+IterNo()-1<=[End Date];
Final:
load [S.No],
[Start Date],
[End Date],
Month,
count(distinct Date) as Days
resident Data
group by [S.No],
[Start Date],
[End Date],
Month;
Now you can simply use sum(Days) in your expression
Thank You @Kushal_Chawda , your solution works like a charm. 😀