Row totals

[Anonymous]

I'm quiet new on this and maby have a simple question:  In a Pivot table I have a row with a condition: If(Column(5)=0, Column(1),0)  But the row totals applies the same condition instead of summing the rows indivdual results.  I have been searching and found several solutions about If and Aggr, but have not been able to get teh row's total correctly calculated.  Please advise if anybody has a solution!  Best regards

[Anonymous]

To be honest (I'm sorry..), I have 4 dimensions: Origen, Articulo, [Cliente NIF], [Grupo Cliente]

Should I just add the other three after Origen??

[Anonymous]

They both don't work, both give 0 on all registers, even adding the four dimensions...

[vishsaggi]

Ok. Can you share your sample file here so that it will be easy to work on quickly. You can scramble confidential data using following instructions here. Preparing examples for Upload - Reduction and Data Scrambling

Also tell us your expected output please...

[Anonymous]

I'm afraid it's a biot complicated. I could quickly resume data and the expected result:

4 dimensions:

Origen

[Articulo]

[Cliente NIF]

[Grupo Cliente]

Then I have:

Y which content is a year

M which contents a mont

D which contents day...

And

[Venta Neta] which refers to sales.

I have alot of more date, but all on the sales level, bu this is my expected result:

I want to show sales per origen (budget, forecasted or actual sales), per article, client or/and client group. And I want to add let's say two columns, one with those clients/articles/clients group who have increasedsales regarding the previous yaer selected, and another column wiht thos who have decreases sales...

It seems simple, but it isn't, at least, not for me!!

Thanks for your help!

[vishsaggi]

Its kind of tough to analyse now where it is going wrong. If you can reduce some data and load only few rows and then upload a sample here that way it would be easy to look into and see where it is going wrong.

[Anonymous]

I have prepared an example.qvw, but how do I upload it??? I'm sorry, I'm new on this...

[sunny_talwar]

Check hereUploading a Sample

[Anonymous]

Got it... Here you are...

The colum increased sales shows data when the conditions of increased sales between 2017 and 2016 are met, but the total doesn't show the correct sum... That's my problem....

[Anonymous]

Thanx Sunny!

[sunny_talwar]

May be using this

If(Dimensionality() = 1, Sum({<Y= {$(=Max(Y)-1), $(=Max(Y))}>} Aggr(If(Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta])<Sum ({<Y= {$(=Max(Y))}>}[Venta Neta]) and Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta])<>0,Sum ({<Y= {$(=Max(Y))}>}[Venta Neta])-Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta]),0), Origen, Articulo)),

If(Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta])<Sum ({<Y= {$(=Max(Y))}>}[Venta Neta]) and Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta])<>0,Sum ({<Y= {$(=Max(Y))}>}[Venta Neta])-Sum ({<Y= {$(=Max(Y)-1)}>}[Venta Neta]),0))