topic Re: Chart Row expression vs Totals Mode issue in QlikView

Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Mon, 29 Apr 2019 11:00:01 GMT

Dears,

I hope the screenshot is self explaining.

My expression for column C is simply deviding 2 columns A and B by eachother

The Total lines of column A and B are set to sum.

How to get the Total line of C to match the total of A / total of B.

I have tried with sum aggr total... but fail to get the result i am looking for.

thanks,

Jan

Re: Chart Row expression vs Totals Mode issue

Vengatesh — Mon, 29 Apr 2019 11:53:06 GMT

Your screen shot is corrupted. Upload a new one.

From my understanding, you can use Expression total or sum of rows in Expression tab Total Mode option.

Re: Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Mon, 29 Apr 2019 11:56:05 GMT

Does this work ?

Re: Chart Row expression vs Totals Mode issue

Vengatesh — Mon, 29 Apr 2019 12:15:08 GMT

Instead of sum of Rows. Use the Above option Expression Total

Re: Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Mon, 29 Apr 2019 14:18:11 GMT

I tried "Expression Total" already.

Its resulting in a '-' as total.

Re: Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Mon, 29 Apr 2019 15:13:44 GMT

Adding a simplified QVW

How to get the totals of A/B --> 11/8 = 1.375

Re: Chart Row expression vs Totals Mode issue

Vengatesh — Tue, 30 Apr 2019 07:21:08 GMT

Is this what you want?

Also you can use Column(1)/Column(2)

Re: Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Tue, 30 Apr 2019 08:56:03 GMT

Unfortunately real life is a bit more complex than the example i attached previously.

I tried to make my demo qvw 1 step closer to what i have in reality...

Putting the SUM(A)/SUM(B) as you proposed is returning "-" in the sample attached same as it does in my real life scenario.

See attached.

Re: Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Tue, 30 Apr 2019 09:16:53 GMT

(SUM({<Type={'SCRAP'}>}Qty)*Price)/((SUM({<Type={'Produced'}>}Qty)-SUM({<Type={'SCRAP'}>}Qty))*Coef)

This is the last expression fully written down.
If i add Sum function around nominator and denominator
SUM(SUM({<Type={'SCRAP'}>}Qty)*Price)/SUM((SUM({<Type={'Produced'}>}Qty)-SUM({<Type={'SCRAP'}>}Qty))*Coef)
the entire table goes blank... you can try on the demo2.qvw attached in post above.
Challenge remains to get the totals of the last column to match this expression.

Re: Chart Row expression vs Totals Mode issue

kushalthakral — Tue, 30 Apr 2019 10:02:49 GMT

Hi Jan

you can use below expression for the attached qvw

If(Dimensionality() = 0,sum( total A)/sum(total B),Sum(A)/Sum(B))

Thanks

Kushal

Re: Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Tue, 30 Apr 2019 10:26:55 GMT

Kushalthakral,

Sorry NOK... see attached demo2.qvw

If(Dimensionality() = 0,sum( total SCRAPPRICE)/sum(total OKCOEF),Sum(SCRAPPRICE)/Sum(OKCOEF)) gives me "-" on each row.

Replacing the column headers by full expressions gives me error "nested agregation"

If(Dimensionality() = 0,sum( total (SUM({<Type={'SCRAP'}>}Qty)*Price))/sum(total ((SUM({<Type={'Produced'}>}Qty)-SUM({<Type={'SCRAP'}>}Qty))*Coef)),Sum((SUM({<Type={'SCRAP'}>}Qty)*Price))/Sum(((SUM({<Type={'Produced'}>}Qty)-SUM({<Type={'SCRAP'}>}Qty))*Coef)))

I disabled this expression in qvw attached.

Thanks for your effort.

Jan

Re: Chart Row expression vs Totals Mode issue

kushalthakral — Tue, 30 Apr 2019 10:41:42 GMT

Can you please use the expression below

=If(Dimensionality() = 0,Sum(Total Aggr(SUM({<Type={'SCRAP'}>}Qty)*Price,Part,Coef)) / Sum(Total Aggr((SUM({<Type={'Produced'}>}Qty) - SUM({<Type={'SCRAP'}>}Qty))*Coef,Part,Coef)),SCRAPPRICE/OKCOEF)

Thanks

Kushal

Re: Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Tue, 30 Apr 2019 12:42:07 GMT

=If(Dimensionality() = 0,Sum(Total Aggr(SUM({<Type={'SCRAP'}>}[#Defected parts amount])*#Price,Tool,#EQU)) / Sum(Total Aggr((SUM([#Produced parts]) - SUM({<Type={'SCRAP'}>}[#Defected parts amount]))*#EQU,Tool,#EQU)),SCRAPPRICE/OKEQU)

Re: Chart Row expression vs Totals Mode issue

Jan_Wilmsen — Tue, 30 Apr 2019 12:42:40 GMT

Thank you Kushaltharal !