one day less

[smilingjohn]

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 

[smilingjohn]

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

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

[Vegar · Accepted solution]

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

[smilingjohn]

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 

[sunsun566]

Hi

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