topic Re: Sum values from other rows in QlikView

Sum values from other rows

Anonymous — Thu, 02 Aug 2018 16:43:51 GMT

Hi everyone,

I'm in the following situation :

I have data that looks like this :

Year	Customer	Account	Amount
2017	CustA	Acc1	1000
2017	CustA	Acc2	1500
2017	CustA	Acc3	500
2017	CustA	Acc4	2500
2018	CustA	Acc1	1200
2018	CustA	Acc2	1200
2018	CustA	Acc3	2000
2018	CustA	Acc4	200
2018	CustA	Acc5	850

Each account is related to a group

Account	Group
Acc1	Group1
Acc2	Group1
Acc3	Group2
Acc4	Group2
Acc5	Group3

Here's what my groups look like :

Group	AlternateGroup
Group1	Group2
Group2	NULL
Group3	NULL

With CustA and 2018 as current Selections, I have been able to produce the following table, EXCEPT for the 2 last columns :

Group	TotalAmount	TotalAmountPreviousYear	AlternateGroup	TotalAmount AlternateGroup	TotalAmount AlternateGroup PreviousYear
Group1	2400	2500	Group2	2000	3000
Group2	2200	3000
Group3	850

I've basically added a crosstable chart to my sheet, and used the followin dimensions and expressions

- Dimensions : Group

- Expressions

- TotalAmount : =Sum({<Year = {"$(=Year)"}>} Amount)

- TotalAmountPreviousYear = =Sum({<Year = {"$(=Year-1)"}>} Amount)

- AlternateGroup = AlternateGroup

I cannot figure out how to build the correct expression for TotalAmountAlternateGroup and TotalAmountAlternateGroupPreviousYear

I've tried =Sum( {$<Group={"=$(AlternateGroup)"}>} Amount) and many other expressions, but nothing works.

When i hard-code =Sum( {$<Group={'Group2'}>} Amount) i get the correct value.

So the problem is, for any given row, to get the sum(Amount) for all the data rows where Group = AlternateGroup of current row.

Any Help would be greatly appreciated

Thanks guys !

Re: Sum values from other rows

sunny_talwar — Thu, 02 Aug 2018 17:36:49 GMT

Are you always going to look at this chart by selecting a year and Customer?

Re: Sum values from other rows

sunny_talwar — Thu, 02 Aug 2018 17:38:06 GMT

Actually even that might not work really... the problem is that the data is in another line and without doing Cartesian product... this might not really work... are you open to doing a Cartesian join in the script?

Re: Sum values from other rows

Anonymous — Thu, 02 Aug 2018 17:58:56 GMT

Thanks Sunny for getting back to me,

Yes the aggregation i need comes from multiple rows in the same table.

In fact i need something that would behave like 'Sum(Amount) Where Group = CurrentGroup'.

Now if there's no way to make an expression work and we need to make some changes in the load script, i'm open to it if it helps solving the problem.

Re: Sum values from other rows

sunny_talwar — Thu, 02 Aug 2018 19:45:13 GMT

Script

Table1:

LOAD * INLINE [

Year, Customer, Account, Amount

2017, CustA, Acc1, 1000

2017, CustA, Acc2, 1500

2017, CustA, Acc3, 500

2017, CustA, Acc4, 2500

2018, CustA, Acc1, 1200

2018, CustA, Acc2, 1200

2018, CustA, Acc3, 2000

2018, CustA, Acc4, 200

2018, CustA, Acc5, 850

];

Table2:

LOAD * INLINE [

Account, Group

Acc1, Group1

Acc2, Group1

Acc3, Group2

Acc4, Group2

Acc5, Group3

];

Table3:

LOAD * INLINE [

Group, AlternateGroup

Group1, Group2

Group2, NULL

Group3, NULL

];

Table4:

LOAD DISTINCT AlternateGroup

Resident Table3;

Left Join (Table4)

LOAD Group as AlternateGroup,

Account as AlternateAccount

Resident Table2;

Left Join (Table4)

LOAD Account as AlternateAccount,

Year as AlternateYear,

Amount as AlternateAmount

Resident Table1;

Chart Dimension

Group

Expressions

=Sum({<Year = {"$(=Max(Year))"}>} Amount)

=Sum({<Year = {"$(=Max(Year)-1)"}>} Amount)

AlternateGroup

=Sum({<AlternateYear = {"$(=Max(AlternateYear))"}>} AlternateAmount)

=Sum({<AlternateYear = {"$(=Max(AlternateYear)-1)"}>} AlternateAmount)

Re: Sum values from other rows

Anonymous — Fri, 03 Aug 2018 15:35:56 GMT

Thank you Sunny,

By the time i got your reply i had figured it out differently.

Here's what i did :

Data1:
LOAD Year, Customer, Account, Amount FROM [$(MyData)] (qvd);
LEFT JOIN LOAD Account, Group FROM [$(MyAccounts)] (ooxml, embedded labels, table is Accounts);
LEFT JOIN LOAD Group, AlternateGroup FROM [$(MyGroups)] (ooxml, embedded labels, table is Groups);

Data2:
LOAD Year, Customer, Account, Amount as AlternateAmount FROM [$(MyData)] (qvd);
LEFT JOIN LOAD Account, Group AS AlternateGroup FROM [$(MyAccounts)] (ooxml, embedded labels, table is Accounts);
LEFT JOIN LOAD AlternateGroup FROM [$(MyGroups)] (ooxml, embedded labels, table is Groups);

Used the same dimensions

Here are the expressions i used :

So for selected Year and Customer i Have

- Expressions

- TotalAmount : =Sum({<Year = {"$(=Year)"}>} Amount)

- TotalAmountPreviousYear = =Sum({<Year = {"$(=Year-1)"}>} Amount)

- AlternateGroup = AlternateGroup

- TotalAmountAlternateGroup =Aggr(Sum({<Year = {"$(=Year)"}>} AlternateAmount), AlternateGroup)

- TotalAmountAlternateGroupPreviousYear =Aggr(Sum({<Year = {"$(=Year-1)"}>} AlternateAmount), AlternateGroup)

And that did the trick !

Of course i checked out your work : brilliant as usual !

It works as well obviously, however i wonder why you use the Max function (e.g Max(Year) or Max(AlternateYear)) ?

Thank you so much for your help. I was stuck with this for hours, happy that i found an elegant way out, with your kind help.

Cheers

Chris

Re: Sum values from other rows

sunny_talwar — Fri, 03 Aug 2018 19:10:24 GMT

It works as well obviously, however i wonder why you use the Max function (e.g Max(Year) or Max(AlternateYear)) ?

Year and AlternateYear will only work if you have one value selected in those fields... but Max(Year) or Max(AlternateYear) will work if you have selected or not selected a value.

Re: Sum values from other rows

Anonymous — Sun, 05 Aug 2018 15:16:09 GMT

Got it ... thanks again Sunny !