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

[swuehl]

Try Company as dimension in your chart and

=FirstSortedValue(Stoc, -Date)

as expression. Take care that your Date field values need to show a numeric representation.

[lavielme]

Can I Do  FirstSortedValue(Stoc, -Date(Date,'DD/MM/YYYY'))

[sunny_talwar · Accepted solution]

May be this:

=FirstSortedValue({<MonthNum = {"$(=Max(MonthNum))"}>}Aggr(Sum(Stoc), Date, Company), -Aggr(Date, Date, Company))

Script:

Table:

LOAD *,

  Month(Date) as Month,

  Num(Month(Date)) as MonthNum;

LOAD * INLINE [

    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

];

Without Selection

With Selection

[swuehl]

If you need to interpete your date values, this should work

FirstSortedValue(Stoc, -Date#(Date,'DD/MM/YYYY'))

But it would be better to do in the script.

SET DateFormat = 'DD/MM/YYYY';

[sunny_talwar]

More like this:

FirstSortedValue(Stoc, -Date#(Date,'DD/MM/YYYY'))

[lavielme]

why the total line is null()???

[swuehl]

You can also try something like

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

Again, take care that Date shows a numeric representation.

[lavielme]

Thank you very much, Sunny. That was the exactly thing that I searched.

Note: Edited by Community Moderator. Please do not post in all capital letters.

[#10]

Hi,

Total null can be set by changing total mode in expression tab from. select "sum of rows".

Thanks