Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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
Highlighted

Re: Expression Help

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

5 Replies

Re: Expression Help

May be this:

If(Status = 'ACTIVE,

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

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

sravanthialuval
Contributor

Re: Expression Help

Hi,

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

Not applicable

Re: Expression Help

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.

Highlighted

Re: Expression Help

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

Re: Expression Help

Thanx Dear....

Community Browser