topic Re: Calculated dimension in QlikView

Calculated dimension

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have a pivot table which shows sales per several existing dimensions (ie customers, articles...), and I'm showing the selected and previous year's sales.

Now I want to create a calculted dimension which indicates if it's a new sale, an increased sale, a lost sale or decreasing sale. I've started to get the first one done, but I'm not able. I think that once that case cleared, I will find teh other cases out by myself...

I'm no using this formula as a calculated dimension:

=Aggr(If(Sum ({<Y= {$(=Max(Y))}>}[Sales])>=Sum ({<Y= {$(=Max(Y)-1)}>}[Sales]),

'New sale'),0)

Re: Calculated dimension

vishsaggi — Mon, 02 Oct 2017 21:04:45 GMT

When you use Aggr you have to mention your dimensions in your Aggr like

Aggr(If(Sum ({<Y= {$(=Max(Y))}>}[Sales])>=Sum ({<Y= {$(=Max(Y)-1)}>}[Sales]),

'New sale',0), YourDimensionFields)

Re: Calculated dimension

Anonymous — Tue, 03 Oct 2017 04:46:38 GMT

Ok, importante detail! Now I have another question: can I change the formula using a variable? I want to offer the user the possibility to chsnge this dimension on his demand. Sometimes I want this dimension to show new sales on one dimension, sometimes the other, and sometimes a combination of two or even three dimension... The formal now is clear. So I've created a variable, VCritDimension, and definied the variable as Article and the formula in

Aggr(If(Sum ({<Y= {$(=Max(Y))}>}[Sales])>=Sum ({<Y= {$(=Max(Y)-1)}>}[Sales]),

'New sale',0), VCritDimension)

But it doesn't work. Why? What should I do ?

Re: Calculated dimension

jonathandienst — Tue, 03 Oct 2017 04:53:13 GMT

If the variable contains a comma separated list of the dimensions, you could expand it like this

=Aggr(If(Sum ({<Y= {$(=Max(Y))}>}[Sales])>=Sum ({<Y= {$(=Max(Y)-1)}>}[Sales]),

'New sale',0), $(VCritDimension))

=Aggr(If(Sum ({<Y= {$(=Max(Y))}>}[Sales])>=Sum ({<Y= {$(=Max(Y)-1)}>}[Sales]),

'New sale',0), $(=VCritDimension))

There a lot of factors to consider here, so I suggest that you upload a sample qvw with some representative data and the sheet object that you are trying to build. It does not need to be real data, but it should illustrate the data structure and the issue.

Re: Calculated dimension

Anonymous — Tue, 03 Oct 2017 06:44:11 GMT

Thanx Vishwarath and Jonathan! I'm gonna work on this, it seems to be the correct way!

Re: Calculated dimension

Anonymous — Tue, 03 Oct 2017 22:06:52 GMT

As a newbee, after some struggling, I've got what I wanted! Just some questions:

In order to easily check, I attach the file

When I choose to apply on the calculated dimension origen, articulo using the button or the other button which calculates the dimension by origen, [Cliente NIF], is there a way to automatically change the order of the two dimensions articulo and [Cliente NIF]? In the first case I want Article in the second column, and [Cliente NIF] in the third, in the other case just the reverse ([Cliente NIF] in the second one, and article in the third column).

And is it also possible to automatically expand??

Re: Calculated dimension

vishsaggi — Wed, 04 Oct 2017 01:32:06 GMT

I am not sure if that possible but a low grade workaround would be using a variable and hide and show two tables. Like in attached.

Create a variable vPosition using Ctrl+Alt+V.

Then when you press 1st the first table will have article as 2nd col and Cliente as thrid. When you press other button it will show reverse.

Re: Calculated dimension

Anonymous — Wed, 04 Oct 2017 17:13:25 GMT

Just by creating the variable vPosition and using it as an action in the Button? Or should I refer to the variable in the table or something like that?? I'm sorry for my ignorance but's it's like chinese for me!!

Re: Calculated dimension

vishsaggi — Wed, 04 Oct 2017 18:22:23 GMT

Yes in your table properties layout tab you can see two radio buttons so use like below

Can you open my qvw file which i attached. If yes then go to chart properties and look for Conditional like above snapshot.

Re: Calculated dimension

Anonymous — Thu, 05 Oct 2017 20:10:26 GMT

Ok, didn't see that, and I didn't knew it...

So, now I have it all as I wanted in the small ejemplo.qvw and now I'm introducing it into the definitive file. And surprise, surprise, I'm having troubles...

The original and working dimension:

=Aggr(If(Sum ({<Y= {$(=Max(Y))}>}[Venta Neta])=0,'- Ventas perdidas',If(Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta])=0,'+ Nuevas ventas',If(Sum ({<Y= {$(=Max(Y))}>}[Venta Neta])>=Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta]),'+ Incremento ventas',

If(Sum ({<Y= {$(=Max(Y))}>}[Venta Neta])<Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta]),'- Disminución ventas',0)))),$(=VCritDimension))

Is working with the vales Sum ({<Y= {$(=Max(Y))}>}[Venta Neta]) and Sum ({<Y= {$(=Max(Y)-1)}>}[Sales]).

In the target file these (working) formulas are:

If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 1])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]))

If(Isnull(subfield(GetFieldSelections(Origen,'||'),'||',2)),

If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta])),

If(subfield(GetFieldSelections(Origen,'||'),'||',2)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',2) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',2))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta])))

But simply replacing the two formulas is not giving any result.

This is the formula:

=Aggr(If(If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 1])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]))=0,'Ventas perdidas',

If(If(Isnull(subfield(GetFieldSelections(Origen,'||'),'||',2)),

If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta])),

If(subfield(GetFieldSelections(Origen,'||'),'||',2)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',2) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',2))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta])))=0,'Ventas nuevas',

If(If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 1])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]))>=If(Isnull(subfield(GetFieldSelections(Origen,'||'),'||',2)),

If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta])),

If(subfield(GetFieldSelections(Origen,'||'),'||',2)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',2) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',2))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]))),'Incremento ventas',

If(If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 1])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]))<If(Isnull(subfield(GetFieldSelections(Origen,'||'),'||',2)),

If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta])),

If(subfield(GetFieldSelections(Origen,'||'),'||',2)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',2) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',2))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]))),'Disminución ventas',0)))),$(=VCritDimension))

Re: Calculated dimension

vishsaggi — Thu, 05 Oct 2017 20:38:07 GMT

Did you create respective variables in your final file where you are copying into? Are the field names same with your example file and your original file?

Re: Calculated dimension

Anonymous — Thu, 05 Oct 2017 20:49:11 GMT

Yes I did. As a matter of fact, the formulas used in the expressions perfectly show the correct numbers...

This formula shows the Sum of [Venta Neta] related to the [Año 1] selected and depends if there is one first selected Origen:

If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 1])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta]))

This formula show teh Sum of [Venta Neta] related the [Año 2] selected and depends if there are one or two origens selected. Both formulas work perfectly...

If(Isnull(subfield(GetFieldSelections(Origen,'||'),'||',2)),

If(subfield(GetFieldSelections(Origen,'||'),'||',1)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',1) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta])),

If(subfield(GetFieldSelections(Origen,'||'),'||',2)='Real' or subfield(GetFieldSelections(Origen,'||'),'||',2) = 'Budget',

Sum ({<Y = {"$(=[Año 2])"}, Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',2))"}>}[Venta Neta]),

Sum ({<Origen = {"$(=subfield(GetFieldSelections(Origen,'||'),'||',1))"}>}[Venta Neta])))

Re: Calculated dimension

Anonymous — Thu, 05 Oct 2017 20:57:25 GMT

Vishwarath, I got it, I have a problem with vCritDimension!

Re: Calculated dimension

vishsaggi — Thu, 05 Oct 2017 21:06:56 GMT

Cool.