How to aggregate by chart dimension?

[Anonymous]

Hi Experts,

I read somewhere that the aggr function ignores dimension. I am having challenges working around this limitation for my chart. Here is my table:

LocationID	Action Type	Action Due Date	Location Size
ABC	A	12/1/2015	5000
ABC	B	12/15/2015	5000
DEF	A	12/1/2015	1500
DEF	B	4/1/2016	1500
GHI	A	12/1/2015	800
JKL	B	9/1/2016	3000

My chart that sums the Location Size by Action Due Date (by Year) is not working correctly.

Dimension = Year(Action Due Date)

Expression = SUM(aggr([Location Size],LocationID))

Using an aggregate because do not want to double count the size of the same location for each year.

For 2015, the sum should be 7300

For 2016, the sum should be 4500

The problem is that on my chart, 2016 is showing as 3000 (i.e. ignoring the 1500 of DEF). It will only correctly show 4500 if the chart is filtered to 2016. It seems the issue is because the record DEF is present in both 2015 and 2016 and the expression is somehow only permitting it to be calculated for one instance.

Any advice? I've attached a working document. Thanks in advance!

[lironbaram · Accepted solution]

have a look at the attach file

i added a year field in the load script

then added the new field to the aggr expression

[Kushal_Chawda]

try below

Dimension = Year(Action Due Date)

Expression =sum(aggr(sum([Location Size]),LocationID, [Action Due Date]))

[tresesco]

Create a year field in the script, like

Year(Date) as Year

and then use expression like:

SUM(aggr([Location Size],Year, LocationID))

[swuehl]

Create the year field in your LOAD script:

LOAD LocationID,

     [Action Type],

     [Action Due Date],

   Year( [Action Due Date] ) as [Action Due Year],

     [Location Size]

FROM

loadfile3.xlsx

(ooxml, embedded labels, table is Sheet1);

Then use this year field as dimension and add it to your aggr() function:

SUM(aggr([Location Size],[Action Due Year],LocationID))

[Gysbert_Wassenaar]

Try sum(distinct [Location Size])