8 Replies Latest reply: Jun 22, 2011 6:27 PM by carhbttqlik RSS

    Script in a multibox

      Hello.

       

      Is it possible to have a script in an expression within a multibox?

       

      I have this expression and is working fine:

       

      =money (Aggr( sum(Income),Service,Year,Quarter), $#,##0.00)

       

      What I need now is to manipulate the values of year and quarter, values of this multibox are taken from a different multibox where I select service, year and quarter so results are displayed in a the multibox where I have the above expression.

       

      Now I want to set up two variables to use within my Aggr function

       

       

      SET PrevYear = Year;

      SET PrevQuarter = Quarter;

       

      if Quarter = 1 then PrevYear = (PrevYear - 1);

      if Quarter = 1 then PrevQuarter = 4 else (PrevQuarter - 1);

       

       

      money (Aggr( sum(Ingresos),Servicio,PrevYear,PrevQuarter), $#,##0.00;-$#,##0.00);

       

      This is not working it appears a dot in the multibox.

       

      How this should be approached?

       

      Best Regards.

        • Script in a multibox
          Johannes Sunden

          I'm not quite following what you're trying to achieve. Do you have an example .qvw to post?

          • Script in a multibox

            I changed my approach and defined two variables in Document properties

             

            PrevYear

            PrevQuarter

             

            if I display both in a multibox they show the right data so I assume that I can use them now in my expression

             

            =money (Aggr( sum(Income),Service,Year={$(=$(PrevYear))},Quarter={$(=$(PrevQuarter))}),

            '$#,##0.00')

             

            However this is not working the multibox just shows -

             

            Any ideas?

              • Re: Script in a multibox
                John Witherspoon

                Well, you have syntax errors above.  You're applying set analysis outside of an aggregation like sum().  It's just sitting there.  I can't quite follow, but maybe you wanted it in the sum?  You might also need single quotes around your literals in the set analysis.  Something like this?

                 

                money(aggr(sum(Year={'$(=$(PrevYear))'},Quarter={'$(=$(PrevQuarter))'} Income),Service),'$#,##0.00')

                  • Re: Script in a multibox

                    John, Johannes, thanks both for your time.

                     

                    I´m attaching my qvw project.

                     

                    Multibox "Document Variables" is just there to show that both variables I declared in document properties are working correctly. Those variables data are the result of "Parámetros" minus one.

                     

                    Multibox "Test" is trying to use those variables to obtain income (Ingresos), the formula there is exactly the same as the one in multibox "Resultado" field 'Ingresos Reales' just using the variables instead of the real fields.

                     

                    I hope this clarifies what I´m trying to do which basically is obtain icome from previous year and quarter than the one selected in multibox "Parámetros"

                     

                    Have a good day.

                      • Re: Script in a multibox
                        John Witherspoon

                        Then the solution I already gave you works.  Translated to your example, it's this:

                         

                        money(aggr(sum({<Año={'$(=$(PrevYear))'},Trimestre={'$(=$(PrevQuarter))'}>} Ingresos),Servicio),'$#,##0.00')

                         

                        For 2011 1, that produces the same possible values as the Ingresos Reales list box does for 2010 4.  That's what you want, right?

                          • Re: Script in a multibox

                            Thank you John, you are right.

                             

                            What I need to understand is the difference between

                             

                            $(PrevYear) & '$(=$(PrevYear))'

                             

                            if you can clarify I will appreciate it.

                              • Re: Script in a multibox
                                John Witherspoon

                                Your PrevYear and PrevQuarter are defined like this:

                                 

                                PrevYear = (Año - 1)
                                PrevQuarter = if(Trimestre = 1,4,Trimestre - 1)

                                 

                                If you just use $(SomeVariable), it will insert the contents of that variable literally.  It won't insert the resulting number.  It will insert the expression itself.  There's no value of Año that is the string literal '(Año - 1)', so this isn't what you want.

                                 

                                If you use $(=SomeExpression), it will actually calculate the expression, and insert the RESULT of that expression literally.  Well, we know how to stick an expression in by using $(PrevYear), so putting it together, $(=$(PrevYear)) will actually do the calculation, and then insert 2010 into the set analysis.  That's what you want.

                                 

                                The single quotes are technically unnecessary since it's a numeric literal and has no spaces or other problematic whitespace.  But I didn't know if you had whitespace in Año or Trimestre when I first proposed the expression.  Also, I've adopted the standard of ALWAYS putting set analysis literals in single quotes to help identify them and simplify future maintenance.  For instance, if you decided to change your Tremestre definition so that it was 'Tremestre 1' through 'Tremestre 4', you'd then need the single quotes.  Rather than require you to check through your document for places you might need to insert single quotes at that time, I just always use the single quotes.