topic Re: Macro development in QlikView

Macro development

Wed, 29 Jan 2014 08:43:56 GMT

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.

Your help would be greatly appreciated.

Regards, Mertin.

Re: Macro development

Colin-Albert — Wed, 29 Jan 2014 08:58:07 GMT

I do not believe this needs a macro.

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.

Re: Macro development

Wed, 29 Jan 2014 09:17:11 GMT

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?

Re: Macro development

Colin-Albert — Wed, 29 Jan 2014 10:00:56 GMT

Add the dimensions to the detailed chart and the sum expression will show the data day by day or transaction by transaction.

What chart expression are you using? Can you post the expression?

Re: Macro development

Wed, 29 Jan 2014 10:50:29 GMT

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

Re: Macro development

Colin-Albert — Wed, 29 Jan 2014 10:55:45 GMT

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.

Re: Macro development

Wed, 29 Jan 2014 12:52:06 GMT

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

' msgbox(fld_cons_year_period(fld_cons_year_period.Count-1).Text)

for cal=left(fld_cons_year_period(fld_cons_year_period.Count-1).Text,4)&"01" to fld_cons_year_period(fld_cons_year_period.Count-1).Text

calselection = calselection&cal&"|"

' msgbox( "("&left(calselection,len(calselection)-1)&")" )

ActiveDocument.Fields("cons_year_period").select "("&left(calselection,len(calselection)-1)&")"

end if

end sub

Your welcome 😜

Re: Macro development

rwunderlich — Wed, 29 Jan 2014 12:56:47 GMT

Your detail chart should be able to display the detail using the same Set expression as the YTD chart. With the additional modifier:

<Date=>

to ignore the Date selection.

-Rob

Re: Macro development

Wed, 29 Jan 2014 13:17:21 GMT

That would be helpful if you want to sum it, but they don't want to, they want to see the detail..

Re: Macro development

Wed, 29 Jan 2014 13:18:57 GMT

Oh, and they only want to see the detail for the YTD, not the full set.. 😉

Re: Macro development

Colin-Albert — Wed, 29 Jan 2014 16:05:56 GMT

All this can be done without macros, just use the functionality of QlikView charts to display the data at the summarised YTD level in one chart and at the detail of the transactions that are being totalled for the YTD figures in another chart, just add the necessary dimensions to view the detail.

Macros should always be a last resort.

Re: Macro development

Thu, 30 Jan 2014 09:33:57 GMT

I agree about avoiding macro's, but this one can't be done without it. I don't have time to set up a sample now to show you, but I've also tried to explain it in every way. But thanks for the effort guys.