topic Re: Pivot table - dynamic sorting using variables - identifying # dimensions in chart in QlikView

Pivot table - dynamic sorting using variables - identifying # dimensions in chart

Thu, 15 Dec 2011 17:05:00 GMT

Hi All,

Am currently trying to build a user defined sorting option within a pivot table using variables.

ie I would like the user to be able to define which expression (volume, value, delta to previous year etc...) they would like the data to be sorted (ascending / descending) by, by using variables.

For a static report (ie one where the number of dimensions included are known), I have managed to get this to work, through re-engineering a solution posted on these forums.

Due to the fact that I want to combine this functionality within a dynamic report, where the number of dimensions will vary depending on user selection, I need to edit my macro so that it recognises that there are X dimensions, and therefore sorts all X dimensions by the user selection...

For some reason, not all my code is being returned when I attempt to copy and paste into Internet Explorer, but is similar to the below:

sub SortMultiple

set chart = ActiveDocument.GetSheetObject("DynamicReport2")

set r = ActiveDocument.Variables("SortName")

set s = ActiveDocument.Variables("SortExpression")

set prop = chart.GetProperties

prop.Dimensions(0).SortCriteria.SortByNumeric = 0

prop.Dimensions(1).SortCriteria.SortByNumeric = 0

if s.GetContent.string = "Value" then

prop.Dimensions(0).SortCriteria.Expression.v "=sum(Euros)"

prop.Dimensions(1).SortCriteria.Expression.v "=sum(Euros)"

else

prop.Dimensions(0).SortCriteria.Expression.v "=sum(QtyKG)"

prop.Dimensions(1).SortCriteria.Expression.v "=sum(QtyKG)"

end if

if r.GetContent.string = "Desc" then

prop.Dimensions(0).SortCriteria.SortByExpression = 1

prop.Dimensions(1).SortCriteria.SortByExpression = 1

chart.SetProperties prop

r.SetContent "Asc", true

else

prop.Dimensions(0).SortCriteria.SortByExpression = -1

prop.Dimensions(1).SortCriteria.SortByExpression = -1

chart.SetProperties prop

r.SetContent "Desc", true

end if

end sub

Would like to adapt the above formula so that it returns "prop.Dimensions(i)" and then loops around for the number of Dimensions ( i), but unsure how to write this...?

Anyone with any thoughts, greatly appreciated!

Kind regards,

Rich

Pivot table - dynamic sorting using variables - identifying # dimensions in chart

Tue, 20 Dec 2011 12:37:50 GMT

could someone kindly help me with my VBA syntax....?

Kind regards,

Rich

Re: Pivot table - dynamic sorting using variables - identifying # dimensions in chart

IAMDV — Tue, 20 Dec 2011 15:08:44 GMT

Hi Rich,

Here you go... I hope this what you needed.

The below procedures calculates the number of dimensions and assigns the count to variable MyDim. You can take this two step further, I mean you can do the same for experssion and dynamically count and add the experssion for sorting. Then you can change the code to generic code so that it works with any chart intead of using the chart name as literal.

'----------------------------------------- Parent Procedure ----------------------------------------------

Public Sub SetObjectToVariables ()

Set MyGraph = ActiveDocument.GetSheetObject("DynamicReport2") 'Assigning the Chart to the variable MyGraph

Set MyDims = MyGraph.GetProperties.Dimensions 'Assigning the Chart Dimension Properties to the Variable MyDims

Set MyExps = MyGraph.GetProperties.Expressions 'Assigning the Chart Expression Properties to the Variable MyExps

Set MyGraphProp = MyGraph.GetProperties 'Assigning the Chart Preoperties to the Variable MyGraphProp

End Sub

'--------------------------------------------------------------------------------------------------------------------

'----------------------------------------- Calling Procedure ----------------------------------------------

Sub SortName

set chart = ActiveDocument.GetSheetObject("DynamicReport")

set r = ActiveDocument.Variables("SortName")

set s = ActiveDocument.Variables("SortExpression")

set prop = chart.GetProperties

prop.Dimensions(0).SortCriteria.SortByNumeric = 0

if s.GetContent.string = "Value" then

prop.Dimensions(0).SortCriteria.Expression.v = "=sum(Euros)"

else

prop.Dimensions(0).SortCriteria.Expression.v = "=sum(QtyKG)"

end if

if r.GetContent.string = "Desc" then

For i=0 to MyDims.Count

prop.Dimensions(i).SortCriteria.SortByExpression = 1 'ascending

chart.SetProperties prop

r.SetContent "Asc", true

i = i + 1

Next

else

For i=0 to MyDims.Count

prop.Dimensions(0).SortCriteria.SortByExpression = -1 'descending

chart.SetProperties prop

r.SetContent "Desc", true

i = i + 1

Next

end if

end sub

'-----------------------------------------------------------------------------------------------------------------------

I totally agree with Steve that is not recommended to use the VBA/VB Scripting approach but I have solved many real life problems using this VB Scripting. So I have love and hate relationship with VBA since my Excel days. I am not sure if VB Scripting in QlikView supports full OOP concepts but I am sure it support object model concept. I always create public procedure & functions outside my calling procedure and call the procedures or functions wherever required by passing the parameters. I am not sure if we can still call it inheritence in OOP concepts. I dont come from computers background so please bear with my ignorance.

I hope this helps!

Let me know if you need anything more in VBA. I'd love to do contribute.

Good luck.

Cheers - DV

Re: Pivot table - dynamic sorting using variables - identifying # dimensions in chart

IAMDV — Thu, 22 Dec 2011 12:39:30 GMT

Hi Rich,

Have you got this working? I am curious to know.

Thanks,

Re: Pivot table - dynamic sorting using variables - identifying # dimensions in chart

IAMDV — Thu, 22 Dec 2011 14:16:56 GMT

Hi Rich,

I am posting the working example here. So that other users can benefit the solution. Please can you mark this thread as answered? I hope its okay.

Thanks,

Re: Pivot table - dynamic sorting using variables - identifying # dimensions in chart

Tue, 21 Feb 2012 15:47:00 GMT

Hi DV,

Apologies re the delay in posting the app, had a bit of tweaking still to be done and then I got side tracked onto other tasks...

The attach uses a dynamic report and then applies your macro to enable a user to display the data in the report according to their needs (ie select an expression to sort by, and whether ascending or descending)....

Kind regards,

Rich

Re: Pivot table - dynamic sorting using variables - identifying # dimensions in chart

IAMDV — Wed, 22 Feb 2012 10:13:47 GMT

Hi Rich,

Nice one. Thank for taking time to share. I like this done of sorting.

Cheers - DV

Re: Pivot table - dynamic sorting using variables - identifying # dimensions in chart

AB108 — Tue, 30 May 2023 21:34:18 GMT

Hi @IAMDV ,

Thanks for coming up with this. I am looking for a similar solution but I have more than a dozen Expressions that I would like to use for sorting. Some of these expressions are calculated off of other expressions. Any idea how can the Macro be modified so that it picks up the expression names and formulae.