5 Replies Latest reply: Sep 3, 2010 7:15 AM by ATucker1 RSS

    Set Analysis when data is a variable

      I have used set analysis to define the below variable called Purchase

       

       

       

       

       



       

      sum

       

       

      ({$<Reregistration={No},[Switch In]={No}>}PurchaseValue)



      and this works fine but what i want to do now is use set analysis to give me the Month to date figure so i have done the below and it works when the data i want to SUM is a data field but in this instance i can't use the data field as i have had to create the variable (called Purchase above)

      So i have typed in vPurchase at the end - example below and tried different variations on this but i can't get it to work

       

      sum({<Year=,Month=,DateCompleted = {"<=$(=date(max(DateCompleted)))>=$(=date(max(yearstart(DateCompleted))))"}>}(vPurchase))



       

      Please help!!!!



        • Set Analysis when data is a variable

          If vPurchase is the variable name then replace this with $(vPurchase)

          • Set Analysis when data is a variable
            Neil Miller

            Is vPurchase the Sum expression from above? If so, your problem is a sum inside a sum. You can't do that unless you utilize the Aggr() function.

            Do you really need the sum inside the sum or are you just looking to include all of that Set Analysis into one expression and using the variable to simplify things? You can set a variables value to be part of your Set Analysis and then use it. Set the value of vPurchase to be:

            Reregistration={No},[Switch In]={No}


            Then use the variable inside your second Set expression:

            sum({<Year=,Month=,
            DateCompleted = {"<=$(=date(max(DateCompleted)))
            >=$(=date(max(yearstart(DateCompleted))))"},
            $(vPurchase)>}PurchaseValue)


              • Set Analysis when data is a variable

                Thanks for this, i have created the variable you suggested and called it vpurcahse2 and put it in the below expression and the table is now giving me values but only for the selected date ie the month to date bit isn't working. Can you see anything wrong with the below?

                 

                sum({<Month=,DateCompleted = {"<=$(=date(max(DateCompleted)))>=$(=date(max(Monthstart(DateCompleted))))"},(vpurcahse2)>}PurchaseValue)

                It is very annoying because when you hover over the top of the table as i haven't named it shows me the selection and the expression is saying 16/04/2010 = 01/04/2010 but is giving me the value of the 16th??!11