Use Amount When Date Is Maximal

[Anonymous]

Hi there,

I have a simple exercise for QV experts...

There is a need to sum up amounts from records with maximal update TimeStamp in case records have same Customer and ID values.

In this case it is absolutely normal to use Set Analysis and Aggregation with Customer and ID as dimensions, but I can't get solution that works. I hope the problem is in syntax, 'cause there is no other idea how to reach my goal.

See attached file to understand what I am talking about.

Rgds,

AT

[swuehl]

A set expression is evaluated per chart, not per line in your chart, so it won't consider the current dimensions values.

I believe you need to use something that takes the current line dimension values into account, maybe you could try FirstSortedValue, like

=Firstsortedvalue(

{<ValidFrom={"<=$(=Max([StatusDate]))"},ValidTo={">=$(=Max([StatusDate]))"},UpdateDate={"<=$(=Max([StatusDate]))"}>}

Amount, -UpdTimeStampNum)

[Anonymous]

Nice advice, swuehl, but the thing is that each Customer can have more than one ID, and in result I have to have a sum of them. And if one ID by Customer has several records, I need to use the newest. So each Customer and ID row will be represented with one record

Is it possible to achieve such functionality with FirstSortedValue? I'm not familiar with this function.

[#4]

i i understand your need corrctly,

i would do this in the load phase.

try to create a flage that holds '1' if conditions are met and '0' if not.

you will have to first load the table and than load it agin using resident and there clculate the flag with if statments.

next, in the dashboard, you can use somthing like sum(if(flag =1,sales,0))

[Anonymous]

If I could solve this thing with Load statement everything would be much easier. User can dynamically change Status Date, that's why some day there could be one record, but another - two. And it could be also situation, when both records are valid, but the update date of the newest is more than Status Date. Than it is out of scope and again we have one record.

[Anonymous]

With my partner's help I found the solution. Too complicated syntax for me:

sum({<

      ValidFrom={"<=$(=Max([StatusDate]))"},

      ValidTo={">=$(=Max([StatusDate]))"},

      UpdateDate={"<=$(=Max([StatusDate]))"},

      UpdTimeStampNum = {'=UpdTimeStampNum=Aggr(nodistinct max({<

                            ValidFrom={"<=$(=Max([StatusDate]))"},

                            ValidTo={">=$(=Max([StatusDate]))"},

                            UpdateDate={"<=$(=Max([StatusDate]))"}

                            >}

                  UpdTimeStampNum),Customer,ID)'}

      >} Amount)

[swuehl]

Artjoms Tukums,

are you sure this gives you valid results when there are multiple records per User and ID? I assumed that there is only at best one record with highest UpdateDate per User and ID and just added some more lines (and got some results that seems not correct to me, see Ins3 in attached sample).

There might also be an issue with a Users & ID having a StatusDate entry same as the StatusDate entry for another combination of Users & ID, but only for one combination it is the highest StatusDate (since the set expression just filters for UpdTimeStatusNum, which itself is not bound to a User & ID combination, or in other words, you are looking for UpTimeStatusNum that are highest for a specific combination of User & ID, but then filter this field on this values for all Users & IDs, right?).

I think you can get a correct result using

=sum(aggr(

Firstsortedvalue(

{<ValidFrom={"<=$(=Max([StatusDate]))"},ValidTo={">=$(=Max([StatusDate]))"},UpdateDate={"<=$(=Max([StatusDate]))"}>}

Amount, -UpdTimeStampNum)

,Customer,ID))

See also attached.

Regards,

Stefan

[Anonymous · Accepted solution]

Thanks to your suggestion, Stefan, I found that my complicated solution doesn't work with some special date variations.

Your last example is perfect in such cases, when only two dimensions can be displayed in Straight Table (Customer and ID)

My users want to see also Valid From and Valid To date and in the future currency code for each ID.

That's why I developed an advanced Load statement with new From and To dates, what with simple expression makes only one Customer&ID row actual on selected Status Date.

A lot of thanks for all your help, Stefan!

Rgds,

AT