Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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?

View solution in original post

8 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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?

johnw
Champion III
Champion III

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')

Not applicable
Author

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.

johnw
Champion III
Champion III

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?

Not applicable
Author

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.

johnw
Champion III
Champion III

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.

Not applicable
Author

John, you are the man, thanks a lot, great explanation perfectly clear.