Calculated Dimension in QlikSense

[venelin_rs]

Hi everybody,

I created Calculated Dimension in QlikSense , but I got error message for Invalid Dimension. I want to show only zero sales and next to suppress null values in the table. I tried with next calcualted dimension :

IF(sum(Sales) = 0 , Product, null() ) 

In expression tab this statement works properly. Version of QlikSense is 2.2.4.

[sunny_talwar]

You need an aggr() function when you do some kind of aggregation in the script. Try this may be:

Aggr(If(Sum(Sales) = 0, Product), Product)

Or add other dimensions if you are checking Sum(Sales) = 0 over multiple dimensions. For instance you want to show products by year

Aggr(If(Sum(Sales) = 0, Product), Product, Year)

[Anonymous · Accepted solution]

Hi, Sunny,

I have a same requirement. And adding Aggr() does make the expression valid in Qliksense, but the if function does not work correctly. Your answer actually missed the "else" of the if case. I found another answer here:
calculated dimension in qliksense

Also did not figure out the correct expression.

I tried/tested as following, but has no luck

only $(=fieldname) or Month, works

=if(GetSelectedCount(fieldname)=1, $(=fieldname), Month) // if case works, else does not work

$(=if(GetSelectedCount(fieldname)=1, fieldname, Month)) // if case works, but the else case dose not work

Aggr(if(GetSelectedCount(fieldname)=1, $(=fieldname), Month), fieldname) //available expression, both if and else does not work

Can you pls have a  check?

Thanks

Zhihong

[MK9885]

Qlik Sense doesn't take Calculated Dimensions.

Maybe take your expression in script and make it as a Dimension?

IF(sum(Sales) = 0 , Product, null() ) as NewProduct

And use New Product in front end as your Dimension.

I'm not sure if Aggr will work in script or no. I guess Aggr or Set functions won't work.

[sunny_talwar]

Have you created a new thread for this? What is $(=fieldname)? why do you need that? I guess I don't really understand what you are trying to do here.... so it would be better if you can create a new thread and post a sample so that its easy for us to understand what exactly are you looking for.

Best,

Sunny