Set analysis question...sets within sets

Wed, 14 Sep 2011 21:31:16 GMT

Hi all --

I'm very new to QlikView and am having trouble with a trend analysis I have to do for our users. In my "transaction" table, I have semi-monthly snapshots of certain data so I always need to pull the most recent set (depending on date selections) in my reports. I understand how to do this and have it working, however I now need to do a monthly trend, so rather than pulling the max transaction date from the table for the date range specified, I have to now find the max date for each month rather than the entire set of data.

I feel like I'm mucking up the scenario a bit, so here's a breakdown of what I'm doing:

Get a set where the transaction type = 'A' for the year of 2011

In cumulative reports, I would further reduce the set to only those transactions where the transaction date = max(transaction date) and calculate the item value.

For the trend report, where I'm using the month/year as a dimension,

I need to look at the set where transaction type = 'A' month by month and calculate the value where the transaction date is the max transaction date for that month (since it's a semi-monthly snapshot there are potentially two different transaction dates per month).

I'm guessing I need to use an aggr function, but I'm not sure where or how to do that.

Has anyone run across a similar scenario and/or have ideas on how to meet this business requirement?

Thanks,

Leigh

Set analysis question...sets within sets

danielrozental — Thu, 15 Sep 2011 00:18:02 GMT

Leigh, yes, you can do this with AGGR but since you have just two snapshots per month wouldn't make more sense to just add a flag to mark the ones that are the max for that month?

Set analysis question...sets within sets

Fri, 16 Sep 2011 13:09:22 GMT

For this particular scenario adding a flag would make the solution easier, yes. However, I fully anticipate a need to use both snapshots, not just the max one, so adding a flag for this one scenario doesn't seem like the optimal solution since it is situation specific.

topic Set analysis question...sets within sets in QlikView

Set analysis question...sets within sets

Set analysis question...sets within sets

Set analysis question...sets within sets