Get Date of Changed Value

[#1]

Hi

I have a data set as below

City	Allocation	Report Date
Biker	AAA	1
Biker	AAA	2
Biker	AAA	3
Biker	BBB	4
Biker	BBB	5

I want to return the last date that the allocation changed, so in this case I want to return a report date of 3 and the allocation value on that date, AAA.

I have supplied an example .qvw, where I have managed to get the above result, but only if I select a specific city (say Biker), so something is wrong! What am I doing wrong? I read something about Aggr() in this regard, but I can't get it to work...

Thanks

Regards

WW

[Jason_Michaelides]

Have you tried:

Max(Aggr(Max([Report Date]),City,Allocation))

or

Max(Aggr(Max([Report Date]),Allocation,City))

Hope this helps,

Jason

[#3]

I have now, Jason. I take it that you meant for the variable MaxReportDate? It didn't work, but thanks for the tip.

[Jason_Michaelides]

Don't know about the variable - I answered from my phone on the train so couldn't open your qvw file. If you've sorted it please mark a correct answer to close the thread.

Jason

[sebastiandperei]

Hi,

In your example, Biker city has the DDD allocation up to 24/01. Then changes to AAA until 11/08, so it changes to DDD again and the records ends in 18/08.

So please, tell me,  what you want to see in your table? In the example shows only the Allocation for the last date (18/8), and this isn't what you said that you need.

If I take in mind you said, the table should give:

Biker     DDD     24/01               (the last date before a change, and last Allocation)

Biker     AAA     11/08               (the last date before a change, and last Allocation)

So, you would like to see that way?

[#6]

Specifically, I want to know

Biker     AAA     11/08               (The last date before a change, and last Allocation)

For each city...

You're correct that the table currently only displays the maximum date. That is just my attempt at solving this problem. That is exactly the error.

I have come to the conclusion that something is off with the variable AllocationAtMaxReportDate. If you clear all selections and set that variable to 'DDD', then the table will show AAA, 11/08 as it is supposed to!?

Why is this happening? Well, I think that for some reason the expression in AllocationAtMaxReportDate seems to be returning a concatenated string of all possible allocations for all cities and dates and not just the allocation for that exact city (the dimension of the straight table) on that exact date (the maximum report date as instructed in the set analysis)...

This must happen because the AllocationAtMaxReportDate expression returns something like AAA, BBB, CCC..., which matches no allocation, and hence no allocation is deselected through the set analysis (-=), and therefore the maximum date will always be the maximum date of all allocations.

So I think I know what the problem is, I just don't know the solution?

Thanks for looking at the problem...

[sebastiandperei]

But, in my last comment, "Biker" has TWO changes (from DDD to AAA, then fomr AAA to DDD), you want to see ONLY LAST change?

And you want to see the date before the change, but the Allocation after the change... is it right?

[#8]

Sebastian Pereira wrote:

But, in my last comment, "Biker" has TWO changes (from DDD to AAA, then fomr AAA to DDD), you want to see ONLY LAST change?

And you want to see the date before the change, but the Allocation after the change... is it right?

Yes, that is correct, I want to see only the last change...

And I want to see the date and allocation before the change...

I want to be able to say, "For Biker on the last report date available (18/08), the allocation was DDD. The previous value held by allocation, which was not DDD, was AAA and was last held on 11/08 (if such a value is indeed available, there might have been no change)."

Thanks

[#9]

Hi,

I attached a modified version of aaa.qvw.

Some thing like that?

Allocation:

I use a concat but use an aggr inside to get te value for each City.

concat (distinct aggr(maxstring({$<Allocation-={"$(=maxstring({1<[Report Date]={'$(=max([Report Date]))'}>}Allocation))"}>} distinct Allocation),City),',')

The Concat is due to your Total row...

Allocation date:

max ({$<Allocation-={"$(=maxstring({1<[Report Date]={'$(=max([Report Date]))'}>}Allocation))"}>}[Report Date])

I don't use a variable. I could put the code there as long as you don execute the expression in the variable...

If you add a flag on the specified rows in the table when loading the script, the GUI would be easier to create...

BR

Hans

[#10]

Hi

It works for Biker, but only Biker (I think it's because you use MaxString on Allocation, which takes all available Allocations (for any city) on 18/08/2012 and not for the specific dimension. Since, Biker is the only City with the Allocation: DDD on 18/08/2012, the expression will only work for Biker).

For example, the City, Boxer, should show Last Allocation Date: 10/06/2012 and Last Allocation: BBB. ¨

Finally, that the total row is displayed is my bad. It's unnecessary. Sorry, I didn't spend long setting up the example.

But thanks for the suggestion.