Aggr function

[#1]

Hi to all,

I tried everything but I can't resolve this issue,hope someone can help me

I have this pivot table.

I'll try to explain what I want to do:

• If I have Dato = 'HC PROCESS FLARING' in a SITE, when DATO = 'HC PROCESS FLARING' only Column "hc flaring sm3" must be populated; when DATO = 'PROCESS FLARING' only column Total Flaring sm3, as shown in Figure.
• If site doesn't have DATO = 'HC PROCESS FLARING' in a SITE I need the same value for both columns.

I hope to do this in front end.

I think something with concat and aggrs, but I can't resolve it...any suggestions?

Thanks in advice!

A.

[alexandros17]

Allega il documento e cercherò di darti una mano ....

[Anonymous]

Hi Antonio,

Can you show me your QVW file ?

Hugs

[#4]

Hi guys, I know it's better but qvw it's 165 Mb, and it's classified

I hope to solve it in pivot table expressions and not in back end, if it's possible, because script it's terrible and huge(not mine, thanks god ).

Thanks for your help!

Antonio

[Anonymous]

A little hint, don't have time at this moment for details...

This expression should return 0 for a SITE  that doesn't have 'HC PROCESS FLARING' and >0 for anyhting else:

=index(concat(total<SITE> DATO, '|'), '|HC PROCESS FLARING|')

[Anonymous]

Hello!

Try these two expressions.

Total Flaring [Sm3]: if(DATO <> 'HC PROCESS FLARING', Sum(Value))

HC Flaring [Sm3]: if(DATO <> 'PROCESS FLARING', Sum(Value))

Of course, substitute "Sum(Value)" with what you are trying to calculate. If they do not work, please post the dimensions and expressions you are using on that table.

Regards.

[#7]

Hi Michael, thanks for your trick, i'll try it tomorrow morning and let you know!

Thanks again!!

[#8]

Hi Bruno, thanks for your reply.

I'm using these two expressions, but this doesn't works in the second half of the pic (green).

Dimensions are Consociata, Site, Gruppo and Dato and two expression are like yours.

Thanks again!

[Anonymous]

Hi, I had not understood very well what were your requirements. I think I know now. Using Michael's tip, let's try this:

Total Flaring [Sm3]: If(Index(Concat(Total<SITE> DATO, '|'), 'HC PROCESS FLARING') > 0, Sum(if(DATO <> 'HC PROCESS FLARING', Value)), Sum(Value))

HC Flaring [Sm3]: If(Index(Concat(Total<SITE> DATO, '|'), 'HC PROCESS FLARING') > 0, Sum(if(DATO <> 'PROCESS FLARING', Value)), Sum(Value))

If it doesn't work, we'll need more information about your data model.

[#10]

Hi Bruno,

doesn't work..

The correct behavior is Site "F", in the others Process Flaring should have both columns with the same value.