Get only first and/or last row of table as total

[praijmakers]

Hello,

I'm struggling with this problem. Please take a look at the example straight table below

Period	#start period	Mutation +	Mutation -	#end period
2018/1	10	3	2	11
2018/2	11	2	7	6
2018/3	6	5	2	9
TOTAL	"first row value" =10	"sum" = 10	"sum" = 11	"last row value" =9

So for the mutation expressions the totals are simply the sum of the rows but for the "#start period" i need the first row value and for "#end period" i need the last row value.

I tried this setting for the expression total mode:

But it gives me the Minimum value from all rows instead of the first row value, which seems odd to me...

Same case for "last string" which gives the maximum value instead of last row value.

Some help on this would be much appreciated.

Best regards,

Peter

[vishsaggi · Accepted solution]

Can you try below using straight table:

Adding Rowno() to the script try like

Total:

LOAD *, RowNo() AS RowNumber INLINE [

Period, #startperiod, Mutation+, Mutation-, #end period

2018/1, 10, 3, 2, 11

2018/2, 11, 2, 7, 6

2018/3, 6, 5, 2, 9

];

Dim: Period

Expr: = IF(Dimensionality()=1, Sum(#startperiod), Sum({<RowNumber = {1}>}#startperiod))

         = Sum([Mutation+])

         = Sum([Mutation-])

         = IF(Dimensionality()=1, Sum([#end period]), Sum({<RowNumber = {"$(=Max(RowNumber))"}>}[#end period]))

[sunny_talwar]

Another option might be using FirstedSortedValue() function

FirstSortedValue(Aggr(Sum(#startperiod), Period), Period)

=Sum([Mutation+])

=Sum([Mutation-])

FirstSortedValue(Aggr(Sum([#end period]), Period), -Period)

[vishsaggi]

Gosh. How did i miss this function .Thanks Sunny.

[praijmakers]

Thanks to both of you!

Sunny your function works when there is only one record per period (like i showed in my example). But it does not work when there are multiple Mutation records per period (which is acctually the case for me) like this:

Total:

LOAD Date#(Period, 'YYYY/M') as Period,

  #startperiod,

  [Mutation+],

  [Mutation-],

  [#end period];

LOAD * INLINE [

    Period, #startperiod, Mutation+, Mutation-, #end period

    2018/1, 10, 3, 2, 11

    2018/1, 10, 1, 1, 11

    2018/2, 11, 2, 7, 6

    2018/3, 6, 5, 2, 8

    2018/3, 6, 1, 2, 8

];

So i went with vishwarath's solution. Only difference there is that i did not need to add  rownumber. Minimum and maximum of Period is allready sufficient:

Thanks again.

[sunny_talwar]

It is not that it doesn't work... it might need a slight modification... for instance if you want to see the sum for the min or max period... you can try this

FirstSortedValue(DISTINCT Aggr(Sum(#startperiod), Period), Period)

If you need only avg value (because they both will be always the same... then you can try this

FirstSortedValue(DISTINCT Aggr(Avg(#startperiod), Period), Period)

Just updated this for the first expression, but the same logic can be used for the last expression as well