Calculated dimension

[Anonymous]

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)

[vishsaggi]

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)

[Anonymous]

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 ?

[jonathandienst · Accepted solution]

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))

or

=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.

[Anonymous]

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

[Anonymous]

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??

[vishsaggi]

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.

[Anonymous]

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!!

[vishsaggi]

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.

[Anonymous]

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))