topic Re: dollar sign expansion inside if statement not working in QlikView

dollar sign expansion inside if statement not working

Mon, 04 Jul 2016 12:26:52 GMT

Hi,

When I use the below expression in a chart, it gives an error that allocated memory exceeded

if(GetSelectedCount(%SubDimension) > 0,
SUM(TOTAL <$(=%SubDimension)> Spend),
SUM(Spend)
)

However, the below expression works

if(GetSelectedCount(%SubDimension) > 0,
SUM(TOTAL Spend),
SUM(Spend)
)

This one alone also works

SUM(TOTAL <$(=%SubDimension)> Spend)

The only problem becomes when I use the dollar sign expansion inside the total and if it is inside an if statement

TOTAL <$(=%SubDimension)>

Any idea how to fix this?

Re: dollar sign expansion inside if statement not working

MK_QSL — Mon, 04 Jul 2016 12:29:21 GMT

What is =%SubDimension?

Calculated Dimension?

Then it will not work... you can't use calculated dimension outside it's definition...

Provide sample data..

Re: dollar sign expansion inside if statement not working

Mon, 04 Jul 2016 12:31:03 GMT

As I said. It works already. It is just dynamically picking up the dimension based on the selection

The only problem is that it is not working inside the if statement. Otherwise, the expression alone works pretty well

Re: dollar sign expansion inside if statement not working

Mon, 04 Jul 2016 12:33:15 GMT

%SubDimension is coming from a field. It has the names of the dimension and when used with dollar sign, will point to the actual dimension

Re: dollar sign expansion inside if statement not working

jyothish8807 — Mon, 04 Jul 2016 12:35:02 GMT

Hi Syed,

Why are you putting $ sign over here ? What kind of field is "%SubDimension" ?

Try Like this:

if(GetSelectedCount(%SubDimension) > 0, SUM(TOTAL <%SubDimension)> Spend), SUM(Spend))

Regards

Re: dollar sign expansion inside if statement not working

Mon, 04 Jul 2016 12:37:38 GMT

Because the %Subdimension field contains the names of the dimension. For example, Supplier, Product etc. When you use it with $ sign, it will refer to the actual dimension. For example, if you select Product from the list, it will as if a product dimension was used in the dimension field. Please read the dollar sign expansion first to understand my problem

Re: dollar sign expansion inside if statement not working

MK_QSL — Mon, 04 Jul 2016 12:37:38 GMT

Try below one..

if(GetSelectedCount(%SubDimension) > 0,

SUM(TOTAL <$(=Concat(DISTINCT %SubDimension,','))> Spend),

SUM(Spend)

)

Re: dollar sign expansion inside if statement not working

MK_QSL — Mon, 04 Jul 2016 12:38:01 GMT

Try below one..

if(GetSelectedCount(%SubDimension) > 0,

SUM(TOTAL <$(=Concat(DISTINCT %SubDimension,','))> Spend),

SUM(Spend)

)

Re: dollar sign expansion inside if statement not working

Mon, 04 Jul 2016 12:41:25 GMT

nope..does not work

Re: dollar sign expansion inside if statement not working

MK_QSL — Mon, 04 Jul 2016 12:42:12 GMT

Provide sample data... or sample app. Difficult to guess and answer..

Re: dollar sign expansion inside if statement not working

Peter_Cammaert — Mon, 04 Jul 2016 12:49:13 GMT

If the variable contains the name of a single dimension field, omit the equal sign inside the $-expansion.

Re: dollar sign expansion inside if statement not working

Mon, 04 Jul 2016 12:52:44 GMT

It is not a variable. It is a field that contains the names of the dimensions. It has the names of six dimensions but the user can select which dimension should the chart show

Dollar equal sign then converts the selected value from the list to the dimension

The expression alone works exactly the way we want. However, inside the if statement, it stops to work if we have this kind of expression

Re: dollar sign expansion inside if statement not working

vinieme12 — Mon, 04 Jul 2016 13:15:04 GMT

Hi Syed,

Is this expression being used in a PIVOT table? with your dimension which holds Supplier, Product values moved to headers?

Re: dollar sign expansion inside if statement not working

vinieme12 — Mon, 04 Jul 2016 13:21:11 GMT

Add two expressions with conditional enabled to overcome the memory issue

Expression1:

Conditional = if(GetSelectedCount(%SubDimension) > 0, 1 , 0)

Expression = SUM(TOTAL <$(=%SubDimension)> Spend)

Expression2:

Conditional = if(GetSelectedCount(%SubDimension) > 0, 0 , 1)

Expression = SUM(Spend)

Re: dollar sign expansion inside if statement not working

settu_periasamy — Mon, 04 Jul 2016 13:22:21 GMT

Hi syed,

I just created the sample. I think, it is working fine for me. Can you provide your sample which is not working for you..

Update:

it is not going to else part, if there is no selection because of null. that will create the expression like

The above one is error expression. that's why getting null result.

Based on Peter's suggestion, you can add the variable and use it in your expression.

Re: dollar sign expansion inside if statement not working

Peter_Cammaert — Mon, 04 Jul 2016 13:31:37 GMT

What happens if you replace $(=%SubDimension) with $(vSelectedDimension) and in Settings->Variable Overview you defined vSelectedDimension as

=%SubDimension

Might be easier to trace substitutions.

Best,

Peter