topic Re: one day less in QlikView

one day less

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,
I need to have a month in the calendare where month should always consider one day less .
like in the month of Janaury we have 31 days and my qlikview should show
Jan:
01-01-2020 to 30-01-2020

Feb:
01-02-20202 to 28-01-2020.

I want to subtract the last day from the month in set analysis .

My expression will be like Count({Dept={'Custom'}>}ID) in this i want to add a month condition which will exclude the last day/date of the month.

Or may be I need a script by which i can create the month field which excludes last date of each month .

how can achive this ?

Thanks in Advance

Re: one day less

smilingjohn — Fri, 16 Oct 2020 06:42:10 GMT

I tried something like this but this did not help me , eroror in expression

Count({< Dept={'Custom'},Date={"<=$(=MonthEnd(Max(Date))-1">}ID)....

Re: one day less

Vegar — Fri, 16 Oct 2020 06:46:29 GMT

What if you tag your last date of the Month in your calendar. You can achieve this by many means, below is one suggestion.

Load

Date,

Year(Date) as Year,

...,

If(Date<=(monthend(Date) - 1), 0,1) as IsLastDayOfMonth

From CalendarSource

Re: one day less

smilingjohn — Fri, 16 Oct 2020 06:59:04 GMT

Thanks @Vegar

By doing this wil i be able to exclude the last date of the month ?

If i select the month Jan then output of my ID's should be display from 1stJan to 30th Jan ( Excluding the ID's on 31st Jan)

Thanks

Re: one day less

sunsun566 — Fri, 16 Oct 2020 08:50:51 GMT

I think it’s easier for you to tag the last day of each month in the qlik script, and then use set analysis.

hope it helps you 🙂

script

chart script