topic Re: Sum if date equals max date per dimension in QlikView

Sum if date equals max date per dimension

nsm1234567 — Fri, 05 May 2017 14:31:01 GMT

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!

Re: Sum if date equals max date per dimension

Anil_Babu_Samineni — Fri, 05 May 2017 15:43:04 GMT

IS this you are expecting?

Re: Sum if date equals max date per dimension

nsm1234567 — Mon, 08 May 2017 05:40:33 GMT

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

Re: Sum if date equals max date per dimension

Anil_Babu_Samineni — Mon, 08 May 2017 05:49:18 GMT

Can you select 4 and then tell us required result

Re: Sum if date equals max date per dimension

nsm1234567 — Mon, 08 May 2017 06:00:26 GMT

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

Re: Sum if date equals max date per dimension

sunny_talwar — Mon, 08 May 2017 13:42:17 GMT

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]))

Re: Sum if date equals max date per dimension

guilhermeluvizo — Tue, 18 Sep 2018 19:28:32 GMT

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.