Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Variable in expression in straight table

Hi

I have managed to setup a variable in a straight table/chart name

='Ageing History Performance ' & '$(vShowChart)'

The Values in vShowChart are 120Days+, 150Days+ and 180Days+

Now I want to add this into an expression as well so I only have one chart, not three. I am missing something somewhere. Please help

The current Expression in the charts are SUM({<Type={'Current'}>}H120DaysPlus), then the second chart SUM({<Type={'Current'}>}H160DaysPlus) and the 3rd chart SUM({<Type={'Current'}>}H180DaysPlus)

I tried this but, the Expression box says the syntax is OK, but it is not givig through any values

SUM(If(Type='Current' and $(vShowChart) = '120Days+', H120DaysPlus),If(Type='Current' and $(vShowChart) = '150Days+', H150DaysPlus),If(Type='Current' and $(vShowChart) = '180Days+', H180DaysPlus)))

1 Solution

Accepted Solutions

Re: Variable in expression in straight table

May be this:

=If(vShowChart = '120Days+', SUM({<Type={'Current'}>}H120DaysPlus),

  If(vShowChart = '150Days+', SUM({<Type={'Current'}>}H150DaysPlus),

  If(vShowChart = '180Days+', SUM({<Type={'Current'}>}H180DaysPlus))))



Best,

Sunny

7 Replies

Re: Variable in expression in straight table

May be this:

=If(vShowChart = '120Days+', SUM({<Type={'Current'}>}H120DaysPlus),

  If(vShowChart = '150Days+', SUM({<Type={'Current'}>}H150DaysPlus),

  If(vShowChart = '180Days+', SUM({<Type={'Current'}>}H180DaysPlus))))



Best,

Sunny

Not applicable

Re: Variable in expression in straight table

I got zero values on this one, sorry, sunindias one is working though. I have an extra question on that one though which I will ask in sunindias answer

Not applicable

Re: Variable in expression in straight table

This works really well

I put this same code into another piece of code, it is working but for sum reason it is not totalling correctly

If(RowNo()=1,(Bottom(If(vShowChart = '120Days+', SUM({<Type={'Current'}>}H120DaysPlus),

  If(vShowChart = '150Days+', SUM({<Type={'Current'}>}H150DaysPlus),

  If(vShowChart = '180Days+', SUM({<Type={'Current'}>}H180DaysPlus)))))), Above(Previous1))

The original code for the 120Day+ is 100% but the moment I add the extra code the figures are wrong. Can you see something obviously wrong which could cause this issue?

If(RowNo()=1,(Bottom(sum({<Type={'Previous'}>} H120DaysPlus))), Above(Previous1))

Re: Variable in expression in straight table

Are you using this expression in a straight table or pivot table???

  • If this is in the straight table, have you tried using Sum for total mode instead of using the expression total.
  • If this is in the pivot table, then you might need to use Aggr function.

In any case, I will need to look at the table to give you better solution.

Best,

Sunny

Not applicable

Re: Variable in expression in straight table

It is a straight table, not sure exactly but what you mean with the sum for total mode. I am attaching a reduced model for you. You will see there is an opening balance which is the previous months closing balance. I brought this in to calculate the Reduction column. I get this data from the previous1 column. It works perfectly if I do a chart per ageing period, i.e. 120, 150, 180, but No I am trying to get smarter code and have one chart instead of three. The other code you gave me for this is in the Actual column. That works perfectly as well.

Hope I am not just missing something silly, or making a obvious mistake.

Compare the first and second straight table for Ageing History Performance 120Days+, the first one has the correct values

If you need to have  a look at the code behind it the hidden script password is Qlikview

Re: Variable in expression in straight table

For Total mode, look at this expression:

Total Mode.PNG

also find attached the application ans see if this is what you want???

PFA

Not applicable

Re: Variable in expression in straight table

Thanks so much for the help

On Fri, Apr 24, 2015 at 2:15 PM, Vikram Vangala <qcwebmaster@qlikview.com>

Community Browser