6 Replies Latest reply: Jan 3, 2014 1:40 PM by Simon Brulotte RSS

    Concatinating modifyers in a set analysis

    Simon Brulotte

      Hi,

      is it possible to concatinate within a set modifyer. For example, our Year-period field is comprised of 7 characters: 2014-06.

      Meaning year 2014, period 6.

       

      I'd like to have a set modifyer that takes the first 5 characters of the selection in "Année-Période financière", for example, it would return "2014-" with the selection of "2014-06", and concatinate that with period 12. This should give the whole year budget in the example below.

       

      sum

       

      ({$< [%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Année-période financière]= {left([Année-période financière],5)&'12'} >} [Montant cumulé aad] * %CR004M)

       

       

      But anyone with skills beyond mine will see that this part:

      [Année-période financière]= {left([Année-période financière],5)&'12'}

       

      Doesn't work.

      What would?

        • Re: Concatinating modifyers in a set analysis
          Stefan Wühl

          Try a dollar sign expansion in your set modifier:

           

           

          sum

           

          ({$< [%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Année-période financière]= {$(=left([Année-période financière],5)&'12') } >} [Montant cumulé aad] * %CR004M)

            • Re: Concatinating modifyers in a set analysis
              Simon Brulotte

              Hi, thanks for the cue.

               

              The expression is OK,  

              sum

               

              ({$< [%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Année-période financière]= {$(=left([Année-période financière],5)&'12')} >} [Montant cumulé aad] * %CR004M)

               

              But it returns nulls.

              If I type in '2014-12' instead, I get the proper values.

              Thanks for the help, I'm still fiddling with the $ expension sign.

                • Re: Re: Concatinating modifyers in a set analysis
                  Peter Cammaert

                  I could be incredibly mistaken but I think set analysis still isn't a row-by-row thing (and left() is not an aggregation function). Your new set criteria are determined at the start of object evaluation. IMHO [Année-période financière] on the right is returning all values and left() has difficulty taking out the first 5 characters.

                   

                  I know it's a lame alternative expression (especially with large data sets) but this one will instantly fix it:

                   

                  sum({$<[%CLE_Type_Transaction_ID] = {'EB', 'EA'}>} if (right([Année-période financière], 2) = '12', [Montant cumulé aad] * %CR004M))

                   

                  I would add a field called Month to your transaction table and use set analysis again with an additional modifier like:

                   

                  ..., [Mois] = {12} >} ...

                   

                  Am I wrong?

                   

                  Peter

                    • Re: Concatinating modifyers in a set analysis
                      Stefan Wühl

                      Right, set analysis as well as the dollar sign expansion is only evaluated once per chart, not per dimension value.

                       

                      I assumed a selection in Année-periode financiére ("...takes the first 5 characters of the selection in ..."), so the expression should work for only a single possible value in this field.

                       

                      What does

                      =left([Année-période financière],5)&'12'

                       

                      return in a text box?

                       

                      You can also try to enclose this in single quotes, maybe QV is taken this as a calculation otherwise:

                       

                      sum

                      ({$< [%CLE_Type_Transaction_ID] = {'EB', 'EA'}, [Année-période financière]= {'$(=left([Année-période financière],5)&'12')'} >}[Montant cumulé aad] * %CR004M)