PivotGrid - Aggr with Dynamics Fields

[#1]

Hi! I need to have an expression using Aggr function.

The problem is that the fields that i need are dynamic, so, i did it in this way but it doesn't work.

Variable:

tDimensionesSelected: It has all fields selected.

      =GetFieldSelections(DimensionesPTO)&','&GetFieldSelections(Dimensiones) &','&GetFieldSelections(DimensionesLIQ)

tDiemsionesSelected_SinProductor: It has all field selected except "ProductorReal"

       =Replace('$(tDimensionesSelected)','ProductorReal,','')

Expression:

     aggr(NODISTINCT

            ($(tMontoTraza_Pura))/($(vKilosLiq)),

             $(tDimensionesSelected_SinProductor)

            )

The result is that i have null values in the pivot grid.

Thsnk

[Gysbert_Wassenaar]

Can you post a small qlikview document that demonstrates the problem?

[marcus_sommer]

AFAIK this won't work because you couldn't use such calculated dimensions within an aggr-function. Why do you want to combine several dimensions into one single dimension? If you used those separately (per variable - to avoid a $-sign expansion) it should work. Maybe these dimension-combining could be (pre-)calculated within the script ...

- Marcus

[Gysbert_Wassenaar]

As I understand it the Dimensiones* fields are fields in which users can select other field names to use as chart dimensions. That should work in principle. But field names with spaces are not taken into account. Maybe that's the problem. Or perhaps one of the other variables is the problem. No way to tell really without looking at the qlikview document.

[#5]

Hi!. I've changed it and now i'm using the Script Fields Name (not the Chart Fields Name).

I've changed some variables too, but it doesn't work yet.

Now, this are the definitions:

tDimensionesPTOFieldName=Concat(DimensionesPTOFieldName,',')

tDimensionesLIQFieldName=Concat(DimensionesLIQFieldName,',')

tDimensionesFieldName=Concat(DimensionesFieldName,',')

tDimensionesSelected: It has all fields selected and i remove the last char that is a ','.

     =left('$(tDimensionesFieldName)' & ',' & '$(tDimensionesPTOFieldName)' & ',' & '$(tDimensionesLIQFieldName)',

       len('$(tDimensionesFieldName)' & ',' & '$(tDimensionesPTOFieldName)' & ',' & '$(tDimensionesLIQFieldName)')-1)

tDiemsionesSelected_SinProductor: It has all field selected except "ProductorReal"

      =Replace('$(tDimensionesSelected)','ProductorReal,','')

Expression:

    aggr(NODISTINCT

            ($(tMontoTraza_Pura))/($(vKilosLiq)),

            $(tDimensionesSelected_SinProductor)

            )

[#6]

Hi Marcus! I can´t pre.calculate in scripts because the result depends of the user dimension selection (is dynamic) (that is why i need to calculate wich dimensions are beeing selected)

I don't undesrtand you when you say that "...to combine several dimensions into one single dimension". I don't want to do that. I need to use AGGR with user dimension selection, except one (ProductorReal)

Thanks

[#7]

Hi! I have news! I'm so close but it still doesn't work

As you can see, i have two variables called: tDimensionesSelected and tDiemsionesSelected_SinProductor

tDiemsionesSelected_SinProductor formula has "tDimensionesSelected".

I tried without using "tDiemsionesSelected_SinProductor " and using only tDimensionesSelected and it works!

So, this one (that i really need), doesn't work

aggr(NODISTINCT

            ($(tMontoTraza_Pura))/($(vKilosLiq)),

            $(tDimensionesSelected_SinProductor)

            )

This one wokrs fine!

aggr(NODISTINCT

            ($(tMontoTraza_Pura))/($(vKilosLiq)),

            $(tDimensionesSelected)

            )

I hope this help! Thanks

[Gysbert_Wassenaar]

Try excluding ProductorReal in the concat expressions:

tDimensionesPTOFieldName=Concat({<DimensionesPTOFieldName-={'ProductorReal'}>}DimensionesPTOFieldName,',')

tDimensionesLIQFieldName=Concat({<DimensionesPTOFieldName-={'ProductorReal'}>}DimensionesLIQFieldName,',')

tDimensionesFieldName=Concat({<DimensionesPTOFieldName-={'ProductorReal'}>}DimensionesFieldName,',')

[#9]

Hi Gysbert! No way. Same result.

Thanks

[#10 · Accepted solution]

Now is working.

Thanks a lot!