9 Replies Latest reply: Oct 7, 2012 3:52 PM by wwieners RSS

    Get Date of Changed Value

      Hi

       

      I have a data set as below

       

      City
      Allocation
      Report Date
      BikerAAA1
      BikerAAA2
      BikerAAA3
      BikerBBB4
      BikerBBB5

       

      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

        • Re: Get Date of Changed Value
          Jason Michaelides

          Have you tried:

           

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

          or

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

           

          Hope this helps,

           

          Jason

          • Re: Get Date of Changed Value
            Sebastian Pereira

            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?

              • Re: Get Date of Changed Value

                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...

                  • Re: Get Date of Changed Value
                    Sebastian Pereira

                    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?

                      • Re: Get Date of Changed Value

                        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

                  • Re: Get Date of Changed Value

                    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

                      • Re: Get Date of Changed Value

                        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.