Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
alexcill
Contributor III
Contributor III

Row totals

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

33 Replies
alexcill
Contributor III
Contributor III
Author

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

alexcill
Contributor III
Contributor III
Author

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

vishsaggi
Champion III
Champion III

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

alexcill
Contributor III
Contributor III
Author

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
Champion III
Champion III

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.

alexcill
Contributor III
Contributor III
Author

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

alexcill
Contributor III
Contributor III
Author

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

alexcill
Contributor III
Contributor III
Author

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