topic Re: Arithmetical operations with table total column in QlikView

Arithmetical operations with table total column

frank1982 — Fri, 23 Mar 2018 10:11:49 GMT

Hi,

for this table:

I have to calculate the TOTAL average price (Column called 'Prezzo Medio') dividing the sum of field 'Imponibile' by the sum of field 'Quantità[kWh]-Perdite'.

In formula, the result of the column 'Prezzo Medio' has to be:

5.893,7/(110.640/1.000*)=53,27

Now, I only calculate the row divisions and aggregate them throw average, that is not the right way.

*1000 is a constant value for a conversion

Could you help me?

Thanks in advance.

Re: Arithmetical operations with table total column

sunny_talwar — Fri, 23 Mar 2018 15:49:14 GMT

May be try like this

Sum(Aggr(Imponibile Expression Here, ChartDimension Here))/(Sum(Aggr(Quantità[kWh]-Perdite Expression Here, ChartDimension Here))/1000)

Re: Arithmetical operations with table total column

frank1982 — Mon, 26 Mar 2018 10:35:25 GMT

Thank you Sunny for the answer.

I tried the formula you suggest me, but it doesn't work.

Probably the problem is linked to the 'if' construct I have builted for the field 'Imponibile' and 'Quantità'.

To explain you better my project, I link you a small example (data and project).

Could you help me?

Thank you very much.

Franco

Re: Arithmetical operations with table total column

sunny_talwar — Mon, 26 Mar 2018 11:36:40 GMT

Try this

Sum(Aggr(

if((Sum(CONSUMO_TOTALE_KWH)/if(Year(DATA_INIZ_FORN)<2017 and (Year(DATA_FINE_FORN)>2017 or isnull(DATA_FINE_FORN)),12,

if(Year(DATA_INIZ_FORN)<2017 and Year(DATA_FINE_FORN)=2017,Month(DATA_FINE_FORN),

if(Year(DATA_FINE_FORN)>2017 or isnull(DATA_FINE_FORN),(12-Month(DATA_INIZ_FORN)+1),(Month(DATA_FINE_FORN)-Month(DATA_INIZ_FORN)+1)))))*12<=10000,

Sum(VENDITA_ENERGIA_MODULAZIONE)

,0)

,FORNITORE, POD))/

Sum(Aggr(

if((Sum(CONSUMO_TOTALE_KWH)/if(Year(DATA_INIZ_FORN)<2017 and (Year(DATA_FINE_FORN)>2017 or isnull(DATA_FINE_FORN)),12,

if(Year(DATA_INIZ_FORN)<2017 and Year(DATA_FINE_FORN)=2017,Month(DATA_FINE_FORN),

if(Year(DATA_FINE_FORN)>2017 or isnull(DATA_FINE_FORN),(12-Month(DATA_INIZ_FORN)+1),(Month(DATA_FINE_FORN)-Month(DATA_INIZ_FORN)+1)))))*12<=10000,

Sum(TOT_CONSUMO_PERDITE)

,0)

,FORNITORE, POD))*1000

Re: Arithmetical operations with table total column

frank1982 — Mon, 26 Mar 2018 12:15:11 GMT

It works!!!

Thank you very much!

Franco