I'm struggling with an interval problem in a set analysis.
I have a chart (table) where i present customer, start date, end date, transaction amount.
the transaction amout needs to be calculated so that the date for the transaction amount is within the start date and end date interval.
start date and end date coming from one table and transactions coming from another table which i have joined in the script.
however, i don't get it to work.
in my chart i se a list of customers and one customer can have several start and end dates and when i try the set analysis it calculates transaction amout between teh lowest of these start dates and the highest of the end dates, not between the two on that one row.
in my chart i se a list of customers and one customer can have several start and end dates and when i try the set analysis it calculates transaction amout between teh lowest of these start dates and the highest of the end dates, not between the two on that one row.
yes, set analysis doesn't depend on dimension dates on the row but on min and max dates of a set
maybe with a sum if
example sum(if(transaction_date>=start_date and transaction_date<=end_date, transaction_amount))
if possible, post a small example of your .qvw or some test data
Just to add clarity and to reinforce the previous answer - the Set Analysis condition is calculated once per chart, before the chart is being calculated and rendered. It cannot be sensitive to the dimensions within the same chart.
One solution is to use sum(if()), however if this is too heavy (in case your dataset is large), you need to use data modeling and associate the range of the start-end dates with the transaction date.
We could possibly look at your specific example if you posted one.
