How to range date field in Set Analysis

[Hatus]

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.

HP

[Gysbert_Wassenaar · Accepted solution]

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

[Hatus]

Hi Gysbert

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

Thank you