topic Re: Straight table calculated total row in QlikView

Straight table calculated total row

tom2qlik — Wed, 14 Jun 2017 09:04:53 GMT

Hi All,

My requirement is to have a calculated total different to it's expression.

My expression is simply calling a field and I want the total of this column to be a calculation of two previously calculated expressions.

The Yield target is not a calculated so the total is not correct as can be seen as blank below.

I would like the Yield Target total to be Standard / Target so it would be 59616.55 / 58008.90

I should also point out that the standard and target columns are both expressions.

Thanks,

Tom

Re: Straight table calculated total row

Anonymous — Wed, 14 Jun 2017 09:07:04 GMT

Neal, didn't this work [Standard]/[Target]??

Re: Straight table calculated total row

Anil_Babu_Samineni — Wed, 14 Jun 2017 09:13:05 GMT

Should be this for Yield Target

Sum(TOTAL [Standard]) / Sum(TOTAL [Target])

Re: Straight table calculated total row

tom2qlik — Wed, 14 Jun 2017 09:16:46 GMT

Unfortunately not, unless I've overlooked something the result I got for Standard/Target is 313% as the Total.

That calculation works for the row values but not the total. The result I should get in the total is 102.7%

Re: Straight table calculated total row

Anil_Babu_Samineni — Wed, 14 Jun 2017 09:27:42 GMT

Seems Working?

Num((Sum(Stadard)/sum(Target)),'#,##0.00%')

Re: Straight table calculated total row

tom2qlik — Wed, 14 Jun 2017 09:35:31 GMT

The Standard and Target columns are expressions.

This is my expression....

Standard = SUM(DISTINCT [Yield.Standard])

Target = sum(TOTAL [Yield.Standard])/sum(TOTAL Yield.Yield_Target)

Yield_Target =

SUM(DISTINCT [Yield.Standard])/
(sum([Yield.Standard])/sum(Yield.Yield_Target))

The result is below

Re: Straight table calculated total row

Anil_Babu_Samineni — Wed, 14 Jun 2017 09:37:56 GMT

Is there any dimensions in your report, Would you able to provide qvw for testing

Re: Straight table calculated total row

Anonymous — Wed, 14 Jun 2017 09:39:51 GMT

Use the columns in the straight table. You can reference earlier columns in the table,

Re: Straight table calculated total row

tom2qlik — Wed, 14 Jun 2017 09:47:26 GMT

How can I reference expression columns in a straight table?

Re: Straight table calculated total row

tom2qlik — Wed, 14 Jun 2017 10:11:42 GMT

Yes here is a test qvw.

Re: Straight table calculated total row

prieper — Wed, 14 Jun 2017 10:25:32 GMT

You need to tickmark Total Mode to "Expression Total"

Peter

Re: Straight table calculated total row

tom2qlik — Wed, 14 Jun 2017 10:29:51 GMT

It didn't make a difference.

Thanks,

Tom

Re: Straight table calculated total row

Kushal_Chawda — Wed, 14 Jun 2017 11:07:54 GMT

try this expression

SUM(DISTINCT [Target Content])/

(sum(aggr(sum([Target Content])/sum(Yield_Target),Resource,Date)))

Re: Straight table calculated total row

Anil_Babu_Samineni — Wed, 14 Jun 2017 11:24:33 GMT

I am not using server now, May be stalwar1‌ cal help you

Re: Straight table calculated total row

sunny_talwar — Wed, 14 Jun 2017 11:29:22 GMT

kush141087‌ response should work here

//Yield_Target

SUM(DISTINCT [Target Content])/

Sum(Aggr((SUM([Target Content])/SUM(Yield_Target)), Resource, Date))

Re: Straight table calculated total row

tom2qlik — Wed, 14 Jun 2017 11:32:41 GMT

Worked perfectly...Thanks!