topic Re: How to display formula expressions depending on dimension values? in QlikView

How to display formula expressions depending on dimension values?

hugmarcel — Mon, 14 Apr 2014 15:01:39 GMT

I have a pivot table with 2 expressions a & b, lets say a=sum(1) and b=sum(2).

Dimension "x" takes 5 values 1, 2, 3, 4, 5.

I want to display expression a for dimension values 1, 2, 4, and expression b for dimension values 1, 2, 3, 5.

How can I do this?

Thx - Marcel

Re: How to display formula expressions depending on dimension values?

its_anandrjs — Mon, 14 Apr 2014 15:04:37 GMT

You can write expression with SET analysis

Dimension:- x

Expression a:- Sum({< x ={1,2,4} > } Value)

Expression b:- Sum({< x = {1,2,3,5}>} Value)

Re: How to display formula expressions depending on dimension values?

Mon, 14 Apr 2014 15:16:57 GMT

set conditionnal expression as match(x,'1','2','3','4','5') and so on

Re: How to display formula expressions depending on dimension values?

hugmarcel — Mon, 14 Apr 2014 15:19:16 GMT

thank you for the proposal, however it does not work. The expression column is still shown, only the values are set to 0. I want to hide the expression column, not just set the values to 0.

thx - Marcel

Re: How to display formula expressions depending on dimension values?

MK_QSL — Mon, 14 Apr 2014 15:20:38 GMT

SUM of What ? Assuming Sales...

SUM({$<x = {'1','2','4'}>}Sales)

SUM({$<x = {'1','2','3','5'}>}Sales)

Re: How to display formula expressions depending on dimension values?

hugmarcel — Mon, 14 Apr 2014 15:24:56 GMT

thank you for the proposal, however it does not work. The expression column is still shown, only the values are set to 0. I want to hide the expression column, not just set the values to 0.

thx - Marcel

Re: How to display formula expressions depending on dimension values?

MK_QSL — Mon, 14 Apr 2014 15:25:56 GMT

Can you provide some sample data in excel file... only 10-15 lines along with your requirements?

Re: How to display formula expressions depending on dimension values?

hugmarcel — Mon, 14 Apr 2014 15:27:25 GMT

I'd like to suppress the columns, not only set the values to 0.

1.) Using match(x,'1','2','3','4','5') in the condition of the formula always removes the formula.

2.) Using match(x,'1','2','3','4','5') in the expression just puts values to 0.

So this is not the solution for my request.

Re: Re: How to display formula expressions depending on dimension values?

hugmarcel — Mon, 14 Apr 2014 15:37:09 GMT

see attachment.

Re: How to display formula expressions depending on dimension values?

MK_QSL — Mon, 14 Apr 2014 15:41:48 GMT

Not possible in Pivot Table as far as you have values for other Dimensions...

Re: How to display formula expressions depending on dimension values?

Mon, 14 Apr 2014 16:04:22 GMT

Hello,

I think I could manage your problem, see the example in attach.

It is not good from performance reasons, but it works.

I wish it helps.

Nik.

Re: Re: How to display formula expressions depending on dimension values?

hugmarcel — Tue, 15 Apr 2014 05:57:47 GMT

a nice solution, i like the pick-method.

thx - marcel

Re: How to display formula expressions depending on dimension values?

MK_QSL — Tue, 15 Apr 2014 06:07:37 GMT

Good Trick !!!

But as you told, performance wise not good and also it's difficult to make it dynamic. i.e. Bigger DataBase...

But still for this post,, good one..