topic Re: PivotGrid - Aggr with Dynamics Fields in QlikView

PivotGrid - Aggr with Dynamics Fields

Thu, 07 Jan 2016 19:47:46 GMT

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

Re: PivotGrid - Aggr with Dynamics Fields

Gysbert_Wassenaar — Sun, 10 Jan 2016 11:07:39 GMT

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

Re: PivotGrid - Aggr with Dynamics Fields

marcus_sommer — Sun, 10 Jan 2016 11:56:03 GMT

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

Re: PivotGrid - Aggr with Dynamics Fields

Gysbert_Wassenaar — Sun, 10 Jan 2016 12:06:19 GMT

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.

Re: PivotGrid - Aggr with Dynamics Fields

Mon, 11 Jan 2016 14:53:25 GMT

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)

)

Re: PivotGrid - Aggr with Dynamics Fields

Mon, 11 Jan 2016 15:04:03 GMT

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

Re: PivotGrid - Aggr with Dynamics Fields

Mon, 11 Jan 2016 15:26:04 GMT

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

Re: PivotGrid - Aggr with Dynamics Fields

Gysbert_Wassenaar — Mon, 11 Jan 2016 17:09:53 GMT

Try excluding ProductorReal in the concat expressions:

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

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

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

Re: PivotGrid - Aggr with Dynamics Fields

Mon, 11 Jan 2016 19:47:57 GMT

Hi Gysbert! No way. Same result.

Thanks

Re: PivotGrid - Aggr with Dynamics Fields

Mon, 11 Jan 2016 20:26:24 GMT

Now is working.

Thanks a lot!