topic Re: Total in staright table in QlikView

Total in staright table

Wed, 25 Nov 2020 16:16:04 GMT

I have the below straight table

The formula for the total column is (days*volume*(0.3))+(days*volume)/150 = total

employee	id	days	volume	total
a	1	27	0.020116677	0.004707302
a	2	3	0.024473813	0.000636319
a	3	94	0.02513826	0.020479303

My output should be as below

employee	total
a	0.025822924

i'm using the below formula i'm getting correct answer if i'm adding the ID field, but i dont want to show ID field and a unique employee

= sum(days* volume*0.3)+ sum(days)*sum( distinct volume) /150

Re: Total in staright table

sunny_talwar — Tue, 11 Oct 2016 19:42:10 GMT

Try this:

=Sum(Aggr(Sum(days* volume*0.3) + Sum(days) * Sum(DISTINCT volume) /150, employee, id))

Re: Total in staright table

Tue, 11 Oct 2016 20:17:25 GMT

Hi Sunny its giving me very high value when compared to the actual value

its giving me 23.050 but the value is 0.65143....

employee

days

volume

total

983

0.764653784219

0.651434047

Re: Total in staright table

sunny_talwar — Tue, 11 Oct 2016 20:37:24 GMT

Would you be able to share a sample where this isn't working?

Re: Total in staright table

shawn-qv — Tue, 11 Oct 2016 23:21:56 GMT

Hi John, are you pre-calculating the total amount in your load script?

If not, I would suggest that you consider moving this calculation to the script.

If you're wanting to calculate it in a chart, then I would imagine an expression like the one below would suffice:

sum(((days*volume*0.3)+(days*volume))/150)

Re: Total in staright table

Anil_Babu_Samineni — Wed, 12 Oct 2016 01:59:46 GMT

Try to create one variable for 0.3 like

LET Vstatic = num('0.3','##.##0')

Then use this variable in to that expression

Re: Total in staright table

hemanthaanichet — Wed, 12 Oct 2016 04:19:21 GMT

Hi John,

Try this..

=Sum(Aggr((Sum(days* volume*0.3) + Sum(days) * Sum(DISTINCT volume)) /150,Employee,Id))

Re: Total in staright table

DavidFoster1 — Wed, 12 Oct 2016 09:56:57 GMT

Sunny T's approach would work if the data was just those 3 rows. I suspect that this is not the raw data (the DISTINCT is the main clue).

If my suspicion is correct, please describe or show us the raw data

Re: Total in staright table

Sat, 15 Oct 2016 16:31:47 GMT

Hi sunny tanks a lot and the problem is resolved now

Re: Total in staright table

Sat, 15 Oct 2016 16:32:50 GMT

Can you please help me out in the below senarios

Creating different kinds of charts and representating data