Sum values from other rows

[Anonymous]

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 !

[sunny_talwar]

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

[sunny_talwar]

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?

[Anonymous]

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.

[sunny_talwar · Accepted solution]

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)

[Anonymous]

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

[sunny_talwar]

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.

[Anonymous]

Got it ... thanks again Sunny !