Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Not easy to explain what I'm trying to do, but I'll do my best.
I have a table chart with few dimensions and measurements. One of the columns in this table is an aggr() that calculate a moving average based on one of the dimensions (Dim1), and it works well.
Now I'm trying make some changes in the table chart to make it dynamic, meaning that the user can select what dimensions\measurements to show (except of the moving average and the month columns that are always there).
Using the help of this post "https://community.qlik.com/t5/QlikView-App-Dev/Passing-the-first-dimension-to-Aggr-function-or-Total... I know how the first dimension to the aggr() without mention it by name using $(=GetObjectField(0)), however it always use the first dimension that is mentioned in the definition of the chart and not the actual one visible to the user.
For example, if in the creation of the table chart I have Dim1, Dim2, Dim3... but the user chose to display Dim2 and Dim3, they see Dim2 as the first column he sees but the GetObjectField(0) still considers Dim1 as the first Dimension.
Anyone has any idea how to pass the "first X visible dimension", so in the example above it would pass Dim2 and Dim3?
Here is the exact syntax of my moving Avg:
=sum( aggr( rangeavg( above( sum({<Month=>}Qty),0,3) ),$(=GetObjectDimension(0)),(Month,TEXT,ASCENDING)))
Thanks
Dror
I think I figure it out by using GetFieldSelections() instead of GetObjectField().
The complete expression is:
=sum( aggr( rangeavg( above( sum({<Month=>}Qty),0,3) ),$(=GetFieldSelections ( _dimensions)),(Month,TEXT,ASCENDING)))
The dimensions I choose in in my _dimensions object are passed to the aggr()
Only small issue is that the order of the dimensions that returned are based on their appearance in the _dimensions object, so if the user rearranges the columns it doesn't affecting the grouping in the aggr(), but I believe I can work with it for now.
I think I figure it out by using GetFieldSelections() instead of GetObjectField().
The complete expression is:
=sum( aggr( rangeavg( above( sum({<Month=>}Qty),0,3) ),$(=GetFieldSelections ( _dimensions)),(Month,TEXT,ASCENDING)))
The dimensions I choose in in my _dimensions object are passed to the aggr()
Only small issue is that the order of the dimensions that returned are based on their appearance in the _dimensions object, so if the user rearranges the columns it doesn't affecting the grouping in the aggr(), but I believe I can work with it for now.