topic set analysis within a dimnesion interval in chart in QlikView

set analysis within a dimnesion interval in chart

Anonymous — Sat, 22 Nov 2014 11:43:44 GMT

Hi,

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.

this is the set analysis i've been using:

sum({$<transaction_date = {">=$(=min(start_date))<=$(=max(end_date))"}>} transaction_amount)

please help.

i've also tries interval match with no luck. but any suggestion will do at this moment!

thanks.

Re: set analysis within a dimnesion interval in chart

maxgro — Sat, 22 Nov 2014 13:54:51 GMT

you said

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

Re: set analysis within a dimnesion interval in chart

Oleg_Troyansky — Sat, 22 Nov 2014 15:26:13 GMT

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.

Re: set analysis within a dimnesion interval in chart

MarcoWedel — Sun, 23 Nov 2014 00:43:46 GMT

if one customer can have multiple start and end dates, then maybe the extended intervalmatch (with an additional dimension) might help.

regards

Marco