SET EXP

[lavielme]

Hey everyone

Lats say i have this data:

Company	Date	Stoc
X	30/05/2016	10
Y	31/05/2016	20
Z	31/05/2016	30
X	30/04/2016	40
Y	01/05/2016	10
Z	02/05/2016	30
X	28/03/2016	40
Y	31/03/2016	50
Z	31/03/2016	10
X	27/03/2016	20
Y	31/03/2016	30
Z	01/04/2016	40

and i need an expression who can Calculate For each Company the last stock in a table for example if i don't choose anything

the table will be like that:

Company     Stoc

X                    10

Y                   20

Z                    30

If i choos march and aprill

Company     Stoc

X                     40

Z                    40

Y                    0

Best Regards

Lavi

[sunny_talwar]

What would you like to see for the total line?

[lavielme]

‌i solved that with aggr thank's

[swuehl]

If you want to filter the month, you can also add the set expression to my last expression:

Sum(

     {<MonthNum = {"$(=Max(MonthNum))"}>}

Aggr( If( Date = Max(Total<Company> Date), Sum(Stoc)), Company, Date))

This should return the correct total, too. And saves you one advanced aggregation

Company	=FirstSortedValue({<MonthNum = {"4"}>}Aggr(Sum(Stoc), Date, Company), -Aggr(Date, Date, Company))	Sum({<MonthNum = {"4"}>} Aggr( If( Date = Max(Total<Company> Date), Sum(Stoc)), Company, Date))
	40	80
X	40	40
Z	40	40

[sunny_talwar]

But Stefan we are already in the straight table, why even need to use Aggr() for that. Why not use the total mode as pointed by Amit‌?

[swuehl]

Which of your aggr() function would you drop then?

[sunny_talwar]

I did not mention dropping any aggr . My comment was related to getting the total right

[swuehl]

Sure, you can change the total mode.

I personally prefer a solution that will also work in a text box or when changing the chart to a pivot table, and if this solution comes with no additional cost, fine.

[sunny_talwar]

I always thought that Aggr() function should be avoided as it can be resource extensive. Although I am using two in my expression, but in general it seems that you are saying that

Avg(Aggr(Sum(Sales), Product)) is better than using Sum(Sales) and using Avg as total mode (in straight table)?

[swuehl]

Hi Sunny,

sorry, it's getting late in here.

Not sure if I understand what you are trying to tell me.

I would agree that in general, I try to avoid the aggr() function since it's creating some overhead and may also show issues w.r.t the (chart) dimensions it's getting projected on.

It all depends on the requirements and context, but if I can chose between two expressions that return the same on the details lines with a similar complexity, but one delivers the correct totals (correct w.r.t to my requirements) and can also be kept when changing e.g. the chart style, I would prefer this over the other.

This does not mean that the other expression is wrong.

[sunny_talwar]

Got it. Thanks for taking out time to go over this in detail for us