topic Re: sum disregarding dimension but not selection in QlikView

sum disregarding dimension but not selection

mall1m — Sun, 13 Jan 2013 22:44:01 GMT

Dear all,

I'm trying to calculate a value along a hierarchy

I have 2 fields: Site and Division and a Value

>> this value is filled only at the Business Unit level and on an empty Site (or a dummy Site if it should help)

I'm trying to display this value for all site linked to the Business Unit without displaying Business Unit in my straight table

For example

Site	Business Unit (not shown in the table)	Value	Display value (awaited result)
	A	120	120
Site A	A	-	120
Site B	A	-	120
	B	90	90
Site C	B	-	90
Site D	B	-	90

i tried several expression combinating TOTAL and set analysis but without success and i didn't manage to find an example that fit with my needsq

sum(TOTAL <Site> Value)

sum({$<Site=,[Business Unit]=P([Business Unit])>} Value)

does anybody has an idea ?

Thanks in advance

Re: sum disregarding dimension but not selection

swuehl — Mon, 14 Jan 2013 00:14:15 GMT

You can try using an advanced aggregation:

=sum(aggr(sum(total<[Business Unit]> Value), [Business Unit],Site))

as expression in a chart with dimension Site.

Re: sum disregarding dimension but not selection

mall1m — Mon, 14 Jan 2013 08:04:53 GMT

unfortunately, it doesn't seem to work, i'm getting 0 on all Site except on the null value Site (that I should hide)

--> on the null Site, i'm also getting the overall total (for each Business Unit)

I'm trying to achive this with another method (island table but it doesn't seem to work as well or by repeating value along all site / all month through JOIN and display the awaited value through an avg())

... if anybody has another solution ...

Re: sum disregarding dimension but not selection

swuehl — Mon, 14 Jan 2013 22:01:37 GMT

It would probably be best if you could create a table for Business Unit and value and one for Business Unit and Site. I think this should solve all your problems:

INPUT:

LOAD * INLINE [

Site,Business Unit,Value

,A, 120

Site A, A,

Site B, A,

,B, 90

Site C, B,

Site D, B,

];

SITES:

LOAD Site,

[Business Unit]

resident INPUT where len(trim(Site ));

VALUES:

LOAD [Business Unit],

Value

Resident INPUT where len(trim(Site ))=0;

drop table INPUT;

Re: sum disregarding dimension but not selection

mall1m — Fri, 18 Jan 2013 09:53:15 GMT

Thanks swuehl I manage to obtain my result with a table storing value at Business Unit level only (so it's the same value for each Site