topic Chart Aggr question in QlikView

Chart Aggr question

maleksafa — Wed, 11 Jun 2014 07:55:16 GMT

Hello,

i am having some problem with the Aggr formula in a chart, my scenario is:

i have a table with two dimensions; Company and Object and one expression which is the amount, now the amount can be positive or negative.

what i am trying to do is to add 2 expressions; the first one giving the sum of the positive amount and the second one showing the sum of the negative amount while displaying only the company as a dimension, that is why i need to aggr on the level of company, object.

to note that the amount is calculated from a variable

//get first the previous years

(Sum({<[Transaction Year]=,[Transaction Month Name]=,[Period Number]=,[Fiscal Year]=,[Transaction Year]={"<$(=max([Transaction Year]))"}, [Act Group]={'CashBank'},[Object Account]=, [Object Account]={1211,1212}>}[Ammount USD])

//now until selected year and month

+Sum({<[Transaction Year]={$(=max([Transaction Year]))},[Transaction Month Name]=,[Period Number]={"<=$(=MAX([Period Number]))"}, [Act Group]={'CashBank'},[Object Account]=, [Object Account]={1211,1212}>}[Ammount USD]))

or else i would have just add sum({<Amount={'>=0'}>}Amount) as the expression

so suppose i have the below table:

Company	Object	Amount
C1	O1	100
C1	O2	-10
C1	O2	123
C2	O2	10
C2	O1	-50

the result will be:

Company	Positive	Negatvie
C1	213
C2	10	-50

thanks.

Re: Chart Aggr question

tresB — Wed, 11 Jun 2014 08:17:10 GMT

Not very sure about your doubt. Do you mean that having issues to include '<Amount={'>=0'}>}' of set analysis into the existing expression? Otherwise, it looks fine for positive figures.

Re: Chart Aggr question

maleksafa — Wed, 11 Jun 2014 08:42:20 GMT

if you take the below example

Company	Object	Amount
C1	O1	100
C1	O2	-10
C1	O2	123
C2	O2	10
C2	O1	-50

for Company C1, Object O2 you have -10 and 123 so the sum will be 113 + 100 from C1,O1 under positive amount, so it is the sum of the amount by object, company. and not the sum of the positive or negative amount only.

as if you are doing the below (step by step):

first calculate the sum of amount by Company, Object:

Company	Object	Amount
C1	O1	100
C1	O2	113
C2	O2	10
C2	O1	-50

and then you will get for each company, the sum of positive amount and sum of negative amount and you will end up with the below

Company	Positive	Negatvie
C1	213	0
C2	10	-50