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?
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?
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.
