Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qv_testing
Specialist II
Specialist II

calculate number of days

Hi Experts,

I'm fadcing issue with number of days calculation,  can you pelase have a look below scenario..

Example: I have start date and end date, here have to calculate Current Year Number of days  and Previous year number of days..

CategorySatrtDateEndDatePeriod
A1/1/20185/22/2019Jan-2018 - May-2019
B8/22/201912/31/2020Aug-2019 - Dec-2020

 

Example 1 : Period =Jan-2018 - May-2019

StartDate = 1/1/2018  and EndDate = 5/22/2019

Current : NUll (we don't have current year)

Previuos Year = 5/22/2019   (May 22nd 2019 - so we have to calculate Year start from May 22 = 141 days )

Example 2: Period = Aug-2019 - Dec-2020

Start Date = 8/22/2019 and EndDate = 12/31/2020

Current Year = 12/31/2020 (Dec 31st 2020 - thus we have to calculate Year Start from 12th Dec = 365 days)

Previous Year = 8/22/2019 (Aug 22nd2019 - so we have to calculate Aug22nd to Dec31 2019 = 131 days (8-22-2019 - 12-31-2019 =131 days))

OutPut:

CategorySatrtDateEndDatePeriodCurrentYearNoofDaysPreviuosYearNoofDays
A1/1/20185/22/2019Jan-2018 - May-2019 141
B8/22/201912/31/2020Aug-2019 - Dec-2020365131

 

Thanks in Advance!

1 Solution

Accepted Solutions
Taoufiq_Zarra

attached a version
you can change in the variables to adjust the results

Capture.PNG

 

let CurrentYear=Date#(YearStart(today()),'MM/DD/YYYY');
let PreviousYear=Date#(YearStart(Makedate(Year(today())-1,1,1)),'MM/DD/YYYY');
let CurrentYear0=Date#(YearEnd(today()),'MM/DD/YYYY');
let PreviousYear0=Date#(YearEnd(Makedate(Year(today())-1,1,1)),'MM/DD/YYYY');

Data:


load *,
if(Year('$(CurrentYear)')=Year(SatrtDate),SatrtDate-'$(CurrentYear)',if(Year('$(CurrentYear)')=Year(EndDate),EndDate-'$(CurrentYear)')) as CurrentYearNoofDays,
if(Year('$(PreviousYear0)')=Year(SatrtDate),'$(PreviousYear0)'-SatrtDate,if(Year('$(PreviousYear0)')=Year(EndDate),EndDate-'$(PreviousYear)')) as PreviuosYearNoofDays
;
load Category, Date#(SatrtDate,'MM/DD/YYYY') as SatrtDate, Date#(EndDate,'MM/DD/YYYY') as EndDate, Period inline [
    Category, SatrtDate, EndDate, Period
    A, 1/1/2018, 5/22/2019, Jan-2018 - May-2019
    B, 8/22/2019, 12/31/2020, Aug-2019 - Dec-2020
];
Regards,
Taoufiq ZARRA

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

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

View solution in original post

3 Replies
Taoufiq_Zarra

can you correct me ?

Here is what I'm getting.

why once you compare with the end of year and once with start of year

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") 😉
qv_testing
Specialist II
Specialist II
Author

Exactly what I need,

as per my scenario need that caluclation. 

Current Year need to be caluclated YearStart and previous year as on date to end of year.

Taoufiq_Zarra

attached a version
you can change in the variables to adjust the results

Capture.PNG

 

let CurrentYear=Date#(YearStart(today()),'MM/DD/YYYY');
let PreviousYear=Date#(YearStart(Makedate(Year(today())-1,1,1)),'MM/DD/YYYY');
let CurrentYear0=Date#(YearEnd(today()),'MM/DD/YYYY');
let PreviousYear0=Date#(YearEnd(Makedate(Year(today())-1,1,1)),'MM/DD/YYYY');

Data:


load *,
if(Year('$(CurrentYear)')=Year(SatrtDate),SatrtDate-'$(CurrentYear)',if(Year('$(CurrentYear)')=Year(EndDate),EndDate-'$(CurrentYear)')) as CurrentYearNoofDays,
if(Year('$(PreviousYear0)')=Year(SatrtDate),'$(PreviousYear0)'-SatrtDate,if(Year('$(PreviousYear0)')=Year(EndDate),EndDate-'$(PreviousYear)')) as PreviuosYearNoofDays
;
load Category, Date#(SatrtDate,'MM/DD/YYYY') as SatrtDate, Date#(EndDate,'MM/DD/YYYY') as EndDate, Period inline [
    Category, SatrtDate, EndDate, Period
    A, 1/1/2018, 5/22/2019, Jan-2018 - May-2019
    B, 8/22/2019, 12/31/2020, Aug-2019 - Dec-2020
];
Regards,
Taoufiq ZARRA

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

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