Count IDs that have bookings within 3 Month of a start date

[GerliW1]

Hello,

I’m struggling with a calculation in a QlikView Chart/Table summing up/counting all bookings (from “bookings” table) done in a specific date range (within 3 months after the booking date of the WelcomeBonus.  The start date “WelcomeBonusDate” is joint to the “Accounts” table.

My expression is perfectly working when just ONE date/account is selected. I assume the correct min start date of the line is considered in this case. As soon as all filters are deleted, there are no resp. wrong values as the expression seems to consider MinDate of the column and not the different lines.

sum(aggr (Count(distinct
     {$ < BookingType = {'Registration'},
     Article_Number  -= {'00123456'},
     TransactionType__c = {'Basic'},
     Booking_Date = {"<=$(=Date(AddMonths(min(WelcomeBonusDate), 3)))"} >}
Bookings.Id), Accounts.Id))

Can you please advise me how to rewrite my expression to really calculate values with each lines minimum start date and not the columns minimum start date?

Thanks a lot in advance!

Gerlinde

[QGTFS]

Hi Gerlinde, one way to debug your issue would be to use a variable and to display its value in a text box in order to pinpoint exactly where it goes wrong. 

Indeed min() works with the minimum value of the field or to say the column. If it's for into the line you want to look you could use Alt(), which behaves like nvl() in SQL.

 Also, if you have a welcome bonus date, I'm not sure that the min() is necessary ?

[GerliW1 · Accepted solution]

Hello!

thanks for your support. Unfortunately it didn't work out so I took the workaround and I included the values already in the skript. 

Best regards

Gerlinde