6 Replies Latest reply: Jul 25, 2011 5:17 AM by Sam Vile RSS

    Is this possible?

      Hi All,

       

      I'm fairly sure what I'm trying to do is possible I just don't quite know the syntax!

       

      I have a table which holds an upload date for the latest instance a KPI has been uploaded. What I need to do is make sure both graphs only show data which matches the latest upload date.

       

      The route I was trying to take was through setting up a variable but I can't quite figure it out...any help is greatly appreciated.

       

      I've attached a dummy QVW for referance.

        • Is this possible?

          Hi Sam,

           

          Try something like this in Expression

          Sum(if([Measure]='FUM' and KPI = 'FUM' and UploadDate = ExtractDate,[Value],0))  and it should work.Presently you do not have data in the existing application for June 2011.   ----------- Talha

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

           

          • Is this possible?

            While this solution works it also takes a long time to calculate...does anyone know of a quicker (more system friendly) way of doing this?

              • Re: Is this possible?
                John Witherspoon

                It's difficult to test since you don't have any data that matches your upload dates, but you should be able to use set analysis to do the same thing as the if() but with higher performance.  Perhaps something like this:

                 

                sum({<Measure={'FUM'},ExtractDate={'$(=date(max({<KPI={'FUM'}>} UploadDate)))'}>} Value)

                  • Re: Is this possible?

                    Thats a good point...I've attached another version of the test QVW as I can't get the set analysis to work.

                     

                    Is it worth stating that I need to the formula to work using the month and year of the upload date...there is a field called UploadMthYr which is basically a concatenation. I think I need that to link to the MonthYear field within the MasterCalendar

                     

                    Thanks for the help John, I really appreciate it.

                • Is this possible?

                  **BUMP**