topic Re: TOTAL and * in expression in QlikView

TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 13:45:00 GMT

Hi,

The following expression doesn't work for me:

sum( Qty*Carts_Per_Item) /

sum(TOTAL <Style> Qty*Carts_Per_Item)

Any idea why?

Thanks!

Re: TOTAL and * in expression

sunny_talwar — Tue, 29 Aug 2017 13:49:36 GMT

Would you be able to post a sample?

Re: TOTAL and * in expression

Miguel_Angel_Baeyens — Tue, 29 Aug 2017 13:57:23 GMT

What do you mean by "it does not work"? Syntax seems to be OK, if it does not return the results you expect then as Sunny suggests post some sample data and expected results.

Are Qty and Carts_Per_Item fields in the same table?

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 14:32:51 GMT

Will try..Thanks!

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 15:09:41 GMT

Hi,

My issue is somewhere else:

I would like to have the relative quantity by dim A,

or share of A

SUM(SUM)/ SUM(TOTAL <A> SUM)

A	B	Qty	%	Total by A
		159413	100.00%	100%
Cl	Bold 1	14660	9.20%	28%
Cl	Regular 1	37362	23.44%	72%
Fl	Mint	8138	5.10%	26%
Fl	Smooth	10222	6.41%	33%
Fl	Summer	12418	7.79%	40%
Me	Bold	17352	10.88%	23%
Me	Regular	52939	33.21%	69%
Me	Smooth	6322	3.97%	8%

Desired number:

Cl	52022	32.63%
Fl	30778	19.31%
Me	76613	48.06%

What am i doing wrong??

Thanks!

Re: TOTAL and * in expression

sunny_talwar — Tue, 29 Aug 2017 15:14:06 GMT

May be you need this

Sum(SUM)/Sum(TOTAL SUM)

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 15:17:46 GMT

Hi,

Yes, in my original model the 2 fields are in different table.

Could that cause an issue?

I simplified it just to check in the above data (sum by dim A), but it doesn't work either,,

Thanks!

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 15:18:57 GMT

No, it gives the same result as the % column..

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 15:21:52 GMT

Just Attached the data..

Re: TOTAL and * in expression

sunny_talwar — Tue, 29 Aug 2017 15:24:41 GMT

This is what you want to get, right?

I guess create a chart with

Dimension

Expression

Sum(SUM)

Sum(SUM)/Sum(TOTAL SUM)

This doesn't give you what you want, really?

Re: TOTAL and * in expression

sunny_talwar — Tue, 29 Aug 2017 15:27:25 GMT

I am getting it with no problem

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 15:30:38 GMT

Hi Sunny,

I have posted the model..

Maybe I miss something silly..

Thanks for your kind help!

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 15:31:17 GMT

Please look at my model. It has dim B too..

Re: TOTAL and * in expression

sunny_talwar — Tue, 29 Aug 2017 15:32:23 GMT

You have used B as dimension, do you really need it?

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 15:36:04 GMT

Unfortunately, yes..

It has to look like that:

A	B	SUM
Cl	Bold 1	14660	33%
Cl	Regular 1	37362	33%
Fl	Smooth	10222	19%
Fl	Summer	12418	19%
Fl	Mint	8138	19%
Me	Smooth	6322	48%
Me	Bold	17352	48%
Me	Regular	52939	48%

Re: TOTAL and * in expression

sunny_talwar — Tue, 29 Aug 2017 15:39:04 GMT

Try this

SUM(TOTAL <A> SUM)/

SUM(TOTAL SUM)

Re: TOTAL and * in expression

dafnis14 — Tue, 29 Aug 2017 15:44:03 GMT

well, your magic touch 🙂

Thanks!

I still have to check now with multiplying by a field from another table.. so

will mark as helpful for the meantime 🙂