2 Replies Latest reply: Jul 18, 2011 10:57 AM by Sunil Kenth RSS

    Help with Set Analysis syntax

    Sunil Kenth

      Hi all,

       

       

       

       

      I am trying set the expression for  a Gauge chart to only calculate for the parent category of 'Telecoms' and for the current year and month.

       

      I have setup variables for the month and year as follows:

       

      vCurrentMonth = (Month(Max(Date))

      vCurrentYear = (Year(Max(Date))

       

       

       

       

       

      ='£'& Num (Sum({$< [Parent Category] = {'Telecoms'}, Month = {$(=$(vCurrentMonth))}, Year = {$(=$(vCurrentYear))}>}[Money Out]))

       

      The result i get for this expression is £0

      Any ideas?

      Thanks

       

        • Help with Set Analysis syntax
          Erich Shiino

          I guess you have a problem with the date format in set analysis.

          In this case, you should create a field on script where month is a number, instead of a formated date.

          The syntax on the script would be:

          Load (...)

          num(Month) as nMonth

           

          from / Resident... etc

           

          Then, the variable would be:

          vCurrentMonth = num((Month(Max(Date)) )

           

          Then, the expression would be:

          ='£'& Num (Sum({$< [Parent Category] = {'Telecoms'}, nMonth = {$(=$(vCurrentMonth))}, Year = {$(=$(vCurrentYear))}>}[Money Out]))

           

          Maybe the expression can be simpler, like this:

          nMonth = {$(vCurrentMonth)}

          or

          nMonth = {$(=vCurrentMonth)}

           

          Hope this helps,

           

          Erich