topic Re: Row totals in QlikView

Row totals

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

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

Re: Row totals

vishsaggi — Wed, 20 Sep 2017 18:23:31 GMT

May be try this?

= Sum(Aggr(IF(If(Column(5)=0, Column(1),0) , yourdimensionshere))

Re: Row totals

Anonymous — Wed, 20 Sep 2017 18:28:13 GMT

I get an error in the expressionn. Some parentheiss missing? I've tried to correct it, but without succes...

Re: Row totals

vishsaggi — Wed, 20 Sep 2017 18:31:16 GMT

Can you share your expression what you have ? Probably a sample upload will help faster to help you.

Re: Row totals

Anonymous — Wed, 20 Sep 2017 18:33:53 GMT

= Sum(Aggr(IF(If(Column(5)=0, Column(1),0) , Origen))

Re: Row totals

vishsaggi — Wed, 20 Sep 2017 18:37:46 GMT

Can you replace those Column(5) and Column(1) with your actual expressions and see if that works.

Re: Row totals

Anonymous — Wed, 20 Sep 2017 18:42:49 GMT

If(Column(5)=0, Column(1),0)

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

So, I should try

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

But I still get an error in the expression. Isn't a ) missing??

Re: Row totals

Anonymous — Wed, 20 Sep 2017 18:45:49 GMT

I've tried:

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

in order to add to the first If an alternative value

Expression is no okl but the result isn't!

Re: Row totals

trdandamudi — Wed, 20 Sep 2017 18:47:17 GMT

I just modified Nagaraju expression.. Can you see if the below works ?

= Sum(Aggr(IF(If(Column(5)=0, Column(1) ),0) , yourdimensionshere))

= Sum(Total IF(If(Column(5)=0, Column(1) ),0) )

Re: Row totals

vishsaggi — Wed, 20 Sep 2017 18:49:37 GMT

Try this?

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

Re: Row totals

Anonymous — Wed, 20 Sep 2017 18:54:21 GMT

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

Re: Row totals

Anonymous — Wed, 20 Sep 2017 18:57:51 GMT

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

Re: Row totals

vishsaggi — Wed, 20 Sep 2017 19:12:53 GMT

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

Re: Row totals

Anonymous — Wed, 20 Sep 2017 19:29:57 GMT

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!

Re: Row totals

vishsaggi — Wed, 20 Sep 2017 21:18:27 GMT

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.

Re: Row totals

Anonymous — Wed, 20 Sep 2017 22:15:29 GMT

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

Re: Row totals

sunny_talwar — Wed, 20 Sep 2017 22:17:21 GMT

Check hereUploading a Sample

Re: Row totals

Anonymous — Wed, 20 Sep 2017 22:21:02 GMT

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

Re: Row totals

Anonymous — Wed, 20 Sep 2017 22:21:37 GMT

Thanx Sunny!

Re: Row totals

sunny_talwar — Wed, 20 Sep 2017 23:10:43 GMT

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