topic How to range date field in Set Analysis in QlikView

How to range date field in Set Analysis

Hatus — Mon, 27 May 2013 13:58:22 GMT

Hi everyone

Can anyone help me to figure out how to solve this expression? In a Pivot Table or Straight Tabel

Sum({<Calendar_BookingDate.Datum = {">=$(=PDGroups.PurchaseDate)<=$(=vKeyDate)"}>} PAYMENTS)

Objective: For each Portfolio (dimension = PDGroups.Portfolio), give me the sum of payments between its Purchase Date (only one different date per Portfolio) and one fix key date equal to all data line.

Problem: I get aways the minimum date of all Portfolios. But when I select only one Portfolio, it returns the right sum.

Interesting is that when I add an expression to the chart with the PDGroups.PurchaseDate alone, it returns the right date per Portfolio regardless the selection.

The Aggr() Function inside this range set didn't work as well.

May any details be missing, pls just let me know.

Thanx in advance.

Re: How to range date field in Set Analysis

Gysbert_Wassenaar — Mon, 27 May 2013 14:09:21 GMT

You found out the hard way that comparing fields on a record-by-record basis in a set modifier doesn't work. You'll have to use an if statement instead. Something like this maybe:

Sum( if( Calendar_BookingDate.Datum >=PDGroups.PurchaseDate and Calendar_BookingDate.Datum <= $(=vKeyDate), PAYMENTS))

Re: How to range date field in Set Analysis

Hatus — Mon, 27 May 2013 14:23:49 GMT

Hi Gysbert

Wonderfull, it works now. I definatelly learned that issue.

Thank you