Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Custom Values in Pivot Table

Hi all

I'm facing an issue here and I need some help from the experts! Well, I have a pivot table with 3 dimensions (for the record, the 3rd is a drill-down group) and 5 expressions. In the 5th expression the ideia is to custom some values:

Here is a samples code:

=if(Cod_Cen1=19,sum(TotalAP)-sum({<Cod_Cen2={47}>}TotalAP),

if(Cod_Cen1=30,Sum(0),sum(TotalAP)))

With this code I want to subtract some cells and to put other with value 0. Until here, everything seems to be working well. However, when I looked at sub-totals, I realize that totals don't change with the customization. Anyone knows why's that? I really need to customization some values on that expression... Nevertheless, If everyone knows another more specific to do that, please let me know.

Thanks in advance!!

Kind regards,

TMF

1 Solution

Accepted Solutions
MVP
MVP

Re: Custom Values in Pivot Table

I'm not sure if I've understood, but the general form for getting sum of rows in a pivot table is to change your expression like this:

sum(aggr(Your Expression, Your Dimensions))

So you may want an expression similar to this, just using your dimensions instead of the ones I threw in there:

sum(aggr(if(Cod_Cen1=19,sum(TotalAP)-sum({<Cod_Cen2={47}>}TotalAP),
if(Cod_Cen1=30,Sum(0),sum(TotalAP))),Customer,Product,Year,Month))

8 Replies
Not applicable

Re: Custom Values in Pivot Table

I believe there is someone who can help me... Anyone?

I really need help with this stuff...

Thnks!

TMF

MVP
MVP

Re: Custom Values in Pivot Table

I'm not sure if I've understood, but the general form for getting sum of rows in a pivot table is to change your expression like this:

sum(aggr(Your Expression, Your Dimensions))

So you may want an expression similar to this, just using your dimensions instead of the ones I threw in there:

sum(aggr(if(Cod_Cen1=19,sum(TotalAP)-sum({<Cod_Cen2={47}>}TotalAP),
if(Cod_Cen1=30,Sum(0),sum(TotalAP))),Customer,Product,Year,Month))

Not applicable

Re: Custom Values in Pivot Table

Hi John

Sorry for only reply now but I'm out of the office...

Your solution works perfectly for my problem!

Thank you for helping me

Kind regards,

TMF

Not applicable

Re: Custom Values in Pivot Table

Hi again John

I'm facing another problem related to this issue. Even if the column I asked you is alright, now I have to do another column with the % of the first, ie., I need to have the proporcion in %. However, as values are custom I don't know how to do this column.... Can you help me?

Thanks in advance!

Best regards,

TMF

MVP
MVP

Re: Custom Values in Pivot Table

How are you calculating the percent now?  Or are you not doing it at all yet?  It looks like QlikView calculates subtotal percents just fine using either column numbers or column labels, even when using weird sum(aggr(...)) expressions.  So one of these for example:

column (3) / column (4)
Profit / Revenue

Not applicable

Re: Custom Values in Pivot Table

Hi John

I'm not doing it at all because I don't know how to do it. However your answer was very useful for future problems!

Well, I think that won't solve my problem. I'll try to explain in more detail what I need to do.

I have a column that is costumized (that one you helped me with sum(aggr(...))) and I need to have another column with the percent of the previous column.

For example:

               Column 1      Column 2

                   10               10/50        

                   20               20/50

                   20               20/50

sub Total      50               50/50

                   30               30/120

                   40               40/120

                   50               50/120

sub Total     120             120/120

Total           170              170/170

Column 1 = Costumized with sum(aggr(...))

I hope I had been more clear

Thanks in advance.

Best regards,

TMF

MVP
MVP

Re: Custom Values in Pivot Table

OK, I think I see what you're asking for.  I believe you could handle it like this:

Column 1 = sum(aggr(Expression,Dimension1,Dimension2))
Column 2 = column (1) / sum(total <Dimension1> aggr(Expression,Dimension1,Dimension2))

Not applicable

Re: Custom Values in Pivot Table

Hi John

It works

Thank you for helping me again

Kind regards,

TMF

Community Browser