I have a model which has a date selection, i.e. 201402.
I have an inline load which gives the user the option to choose from current Movement, YTD or Closing Balance.
When the user selects YTD for instance, the set analysis works out totals based on variables which are derived from the currently selected date.
The problem is that the user then wants to see detailed transactions for that date range, without the date range being selected. The transactions, which are in a flat table will only show for the selected date, even though the graph shows YTD.
I was hoping to create a trigger on change of the field which lists the option for YTD, Movement and Closing Balance, so that if the user selects YTD, it will automatically select all dates up to the selected date for the current year.
Unfortunately I don't know how one would build a macro for this and I don't know of any other way to do this.
You just need two charts, one is the existing YTD chart, the detailed chart is a clone of the YTD chart with the date and/or unique transaction ID added as additional dimensions.
The user can then select which chart to view, or add both charts to a container.
The user selects i.e. 201409, looks at the chart with the totals and sees aggregated totals from 201401 to 201409. But only 201409 is selected. If you look at the detail records, then you will only see the records for that period. How will you see the records from 201401?
The detail records has all the dimensions and no sums. It's just the detail. There is just one date selected, i.e. 201409. The other chart sums values based on that date, by using variables, it works out the first date, i.e. 201401 and then shows the total using set analysis. When you look at the detail table, the model is still filtered to the one date 201409, so the detail for the other months won't show, but the user wants to see them when choosing the indicator for YTD. (The set analysis in the summary chart also checks the indicator.)
Try following the steps suggested by creating a new detailed chart as a clone of the YTD chart with the extra dimensions and the expression.
Macros should always be a last resort in QlikView as they remove a lot of the caching and report optimisation. I rarely use macros in most cases they are not needed.
There are no dimensions to add, they are all in the detail and the expression is not applicable because summing on detail records won't give anything other than the detail itself. Putting in a sum on the detail level with set analysis included for the range period, won't make the model display all the detail records for the range, it will still only display as per the selected period.
A colleague of mine has managed to build the macro that we need though and it looks like this:
sub ytdSelect
if ActiveDocument.ActiveSheet.GetProperties.Name = "GL Transactions" then
set fld_baltype = ActiveDocument.Fields("Balance Type").GetPossibleValues
for i = 0 to fld_baltype.Count-1
if fld_baltype(i).Text = "YTD" then
set fld_cons_year_period = ActiveDocument.Fields("cons_year_period").GetPossibleValues
