Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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
MVP
MVP

Re: Script in a multibox

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?

8 Replies
jsn
Honored Contributor

Script in a multibox

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

Not applicable

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?

MVP
MVP

Re: Script in a multibox

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

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.

MVP
MVP

Re: Script in a multibox

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

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.

MVP
MVP

Re: Script in a multibox

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

Re: Script in a multibox

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