Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
GerliW1
Contributor
Contributor

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

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

Labels (2)
1 Solution

Accepted Solutions
GerliW1
Contributor
Contributor
Author

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

View solution in original post

2 Replies
QGTFS
Contributor III
Contributor III

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
Contributor
Contributor
Author

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