Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Expression Help

Dear Experts,

I used following expression in my application for a date calculation.

(IF(STATUS='ACTIVE', NUM(TODAY())-(DATE(DATE#(LON_DATE))),DATE(DATE#(SET_DATE))-DATE(DATE#(LON_DATE)))+1)

This expression counts the number of days from the 'LON_DATE' to the current date including 'LON_DATE'.

So now i need to remove(Exclude) the all "Saturdays and Sundays" comes after 'LON_DATE' from this calculation.

How i can do this with Qlick view.

Rgds,

Priyantha.

1 Solution

Accepted Solutions
sunny_talwar

Not sure what you chart dimensions are, but assuming you have Dim1 and Dim2.... you need this

Sum(Aggr(

If(Status = 'ACTIVE,

    NetWorkDays(Date(Date#(LON_DATE)), Today()),

    NetWorkDays(Date(Date#(LON_DATE)), Date(Date#(SET_DATE)))) + 1


, Dim1, Dim2))

View solution in original post

5 Replies
sunny_talwar

May be this:

If(Status = 'ACTIVE,

     NetWorkDays(Date(Date#(LON_DATE)), Today()),

     NetWorkDays(Date(Date#(LON_DATE)), Date(Date#(SET_DATE)))) + 1

sravanthialuval
Creator
Creator

Hi,

By using Networkdays() function we can exclude all "Saturdays and Sundays".

Not applicable
Author

Dear Sunny,

Thanks lot for early reply.

I have achieved the expected output with your suggestion. But the expression does not support with the subtotals.

As i think it required a small modification.

Can you help to find the relevant modification?

Thnx & Rgds,

Plriyantha.

sunny_talwar

Not sure what you chart dimensions are, but assuming you have Dim1 and Dim2.... you need this

Sum(Aggr(

If(Status = 'ACTIVE,

    NetWorkDays(Date(Date#(LON_DATE)), Today()),

    NetWorkDays(Date(Date#(LON_DATE)), Date(Date#(SET_DATE)))) + 1


, Dim1, Dim2))

Not applicable
Author

Thanx Dear....