Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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
try below
Dimension = Year(Action Due Date)
Expression =sum(aggr(sum([Location Size]),LocationID, [Action Due Date]))
Create a year field in the script, like
Year(Date) as Year
and then use expression like:
SUM(aggr([Location Size],Year, LocationID))
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))
Try sum(distinct [Location Size])