Show list of dates "less than"

[JustinDallas]

Hello Everyone,

So I have a a bunch of dates in a Timeline table.  The table contains many fields, but also 3 important fields, InvoiceDate, BillingDayMonth# (Billing Day of the month) and  PMTD (Previous Month to Date). 

What I want to do is show the list of Invoice Dates that are less than or equal to 10 Billing Days (BillingDayMonth#  <= 10), and of the last month PMTD={1}.  I can figure out how to do this in set analysis, but I can't figure out how to do it in an Expression for a list.

Any help in this matter is greatly appreciated.

[avinashelite]

Hi,

I am bit confused about your requirement , you want invoice date < 10 billing dates?? so for 10 different dates you want the invoice dates less than that??? if so How your defining  the 10 bill dates ??

[JustinDallas]

Okay, so for instance where I am, it's February 16th, 2015.  If we look at the calendar, we see that out of the 16 days, we have 10 weekdays, and no holidays.  So Feb 16th 2015 has a BillingDayMonth# of 10.  Long story short, BillingDays is a count of the days in the month/year that aren't weekends, and aren't holidays.  My table would look something like this, with many more fields.

Date	BillingDayMonth#
02/02/2015	1
…	…
02/15/2015	9
02/16/2015	10

[senpradip007]

Use NetWorkDays()

[senpradip007]

PFA. Hope it will help.

[jonathandienst]

Hi

I am not sure what you are asking for, as you seem to have the data you need and know how to use the data in a set expression.

This is an expression that will work out the equivalent working day of the previous month:

=LastWorkDate(MonthStart(Today(), -1), NetWorkDays(MonthStart(Today()), Today()))

NetworkDays works out the number of working days so far this month, and LastWorkDate caclculates the date for that number of working days last month. Today (17 Feb), this returns 15 January.

This will fail near the end of the month if the number of workdays in the prior month is too low (eg March vs Feb), so you might need a RangeMax(...., MonthEnd(Today(), -1)) to clip the results to the end of the month.

HTH

Jonathan

[JustinDallas]

Never mind, I didn't understand Qlikview to accomplish what I thought I needed.