Sum if date equals max date per dimension

[nsm1234567]

Hi There,

I'm hoping someone can assist with an issue I'm having putting together a fairly complex expression.  If you look at the attached .qvw, you'll see I have two suppliers.  The max date for MySupplier 1 is 20161008 and the max date for MySupplier 2 is 20161022 (as you can see from the "Max Date Per Supplier" expression).  What I want to do is sum the value where the date is equal to the max date for each MySupplier.

If you look at the hardcoded date expressions, what I want is a single expression that gives 2145432.66 for MySupplier 1 and 144478.62 for MySupplier 2 (highlighted in image attached).  The big complication is the way the underlying data is structured and I'm not able to figure out how to create the expression to accommodate this. 

Any assistance would be greatly appreciated!

[Anil_Babu_Samineni]

IS this you are expecting?

[nsm1234567]

Hi Anil,

Thanks for the response.  That isn't quite what I'm looking for.  Ideally, the final view would look like the screenshot below.  The Max Date that should be used is the "Max Date Per Supplier" which could be different for each supplier.  The below example has only two dates, but there could be many max dates for different suppliers

[Anil_Babu_Samineni]

Can you select 4 and then tell us required result

[nsm1234567]

I've added a screenshot for Fin Month 3 (I unfortunately don't have the data in the model required to do Fin Month 4).  So in this case the max date (or Max(SK Ldow No) ) is 20161224 for supplier 1 and 20161210 for supplier 2

[sunny_talwar]

May be a slight change in the data model where you add this to your script

Left Join (TEST)

LOAD Distinct [SK Style No],

  MySupplier as MySupplierNew

Resident TEST

Where Len(Trim(MySupplier)) > 0;

And then this as your expression

=FirstSortedValue(Aggr(If(Len(Trim(MySupplier)) > 0, Sum(Value)), MySupplierNew, [SK Ldow No]), -Aggr(If(Len(Trim(MySupplier)) > 0, [SK Ldow No]), MySupplierNew, [SK Ldow No]))

[guilhermeluvizo]

Hi, There.

I have a similar question.

I have to create a stock control, and I need to se the stock value per max date dimension.

But I can't create an expression that show the Stock Value at the Max Date per Month.

I Tried this follow expression

=sum(if('DATE'=max(total('DATE')), Value))

But wasn't work.