topic Re: Master Calendar and Set Analysis in QlikView

Master Calendar and Set Analysis

Anonymous — Thu, 20 Feb 2014 21:30:51 GMT

Hello,

I'm having an issue comparing dates and getting sums of amounts using a master calendar.

I'm trying to find all open amounts as of a current date.

For example, if I select 12/31/13 as my date, I want to see all open amounts as of that date but I can't get it to work using set analysis.

Here's my current expression:

Sum({$<TRANSDATE = {"<=$ (=Master_Calendar.Date)"} >} AMOUNT)

Currently, I'm only getting 0's when running the application.

Any help is greatly appreciated.

Thank you in advance!

Re: Master Calendar and Set Analysis

Colin-Albert — Thu, 20 Feb 2014 21:39:25 GMT

try Sum( {$ <TRANSDATE= {"<=Master_Calendar.Date"} >} AMOUNT)]

or Sum({$<TRANSDATE = {"<=$(=Master_Calendar.Date)"} >} AMOUNT)

Your expression has an extra space between $ and (=

$(= must be entered without spaces between the 3 characters.

Re: Master Calendar and Set Analysis

maxgro — Thu, 20 Feb 2014 21:47:11 GMT

Sum( { $<TRANSDATE ={"<=$(=max(Master_Calendar.Date))"} >} AMOUNT)

Re: Master Calendar and Set Analysis

Thu, 20 Feb 2014 21:58:26 GMT

I have one question

is there any field to find which amount is open and which is closed????

Re: Master Calendar and Set Analysis

Anonymous — Thu, 20 Feb 2014 22:22:29 GMT

Thank you, this works! Would you know if there's a way to get a range in the expression?

For instance, I'm trying to find amounts where if a transaction date is less than the master calendar date and the due date is 30 days before the master calendar date, show the amount.

I'm thinking it would be something like this but again, it's all 0's:

Sum({$<TRANSDATE={"<=$(=max(Master_Calendar.Date))"}, DUEDATE = {"$(=Master_Calendar.Date - DUEDATE >= 30)"}>}AMOUNT)

Sorry if it seems like I'm asking a lot, I'm still pretty new to Qlikview.