How to count no of Sundays between two dates in qliksense

[charu16aug]

How to count no of Sundays between two dates in qliksense

[Rams-]

Hi,

You have to create WeekendFlag in your script:

if(WeekDay(yourdatefield)='Sun',1,0) as WeekendFlag

And in your frontend use the below expression

count({1<WeekendFlag={'1'}, Date={">=$(=your min date)<=$(=your max date)"}>}WeekendFlag)

Hope this helps,

help user find answers ! don't forget to mark  a solution that work for you and click the like button!

[charu16aug]

i have to perform this on backend

[Rams-]

You have to add 

WeekDay(your date field) as WeekDay;

in master calendar.

Then insert

if(WeekDay(yourdatefield)='Sun',1,0) as WeekendFlag,

in your table.

These task will be in your backend 

[charu16aug]

then how to count no. if Sundays between two dates in the backend?

[Rams-]

count({1<WeekendFlag={'1'}, Date={">=$(=your min date)<=$(=your max date)"}>}WeekendFlag)

In frontend this expression will give you the count of Sundays

[charu16aug]

this is an intermediate step, so i want to count this in backend only

[Kushal_Chawda · Accepted solution]

@charu16aug  If you want to do it in backend I am assuming that you have StartDate and EndDate in your data. You can try below

let vWeekDayNumber= 6; //0-Mon,1-Tue,2-Wed....
set NoOfSunday = 'Div(num(WeekDay($1-($(vWeekDayNumber)+1),0))+1-$1+$2,7)';

Data:
Load *,
$(NoOfSunday(StartDate,EndDate)) as  NoOfSunday
Inline [
StartDate,EndDate
01/03/2023, 01/05/2023
01/04/2023, 01/05/2023 ];

[charu16aug]

thanks @Kushal_Chawda 

[MarcoWedel]

Div(EndDate-StartDate+WeekDay(StartDate)+1,7)