Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Show list of dates "less than"

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.

6 Replies
Highlighted

Re: Show list of dates "less than"

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

Highlighted
Specialist II
Specialist II

Re: Show list of dates "less than"

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.

DateBillingDayMonth#
02/02/20151
02/15/20159
02/16/201510
Highlighted
Specialist III
Specialist III

Re: Show list of dates "less than"

Use NetWorkDays()

Highlighted
Specialist III
Specialist III

Re: Show list of dates "less than"

PFA. Hope it will help.

Highlighted
MVP
MVP

Re: Show list of dates "less than"

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Specialist II
Specialist II

Re: Show list of dates "less than"

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