topic Table with multiple expression and two columns of data in QlikView

Table with multiple expression and two columns of data

Mon, 14 Apr 2014 10:43:20 GMT

Hi community,

I am calculating two columns of data 5 different business areas. However, every single value displayed in both columns is the result of a unique calculation, as the data is drawn from various external tables.

Obviously i can display all the expressions in one column easily enough, but how can I designate certain expressions to appear in column A and others in column B when there are no directly common fields in the underlying data.

Category	revenue (column A)	% (column B)
A	£1,232,232	5.31
B	£676,343	6.47
C	£52,356	3.19
D	£8,632,754	7.27
E	£6,754	6.61

Re: Table with multiple expression and two columns of data

Mon, 14 Apr 2014 11:14:27 GMT

Do you need unique expressions for each Category, or simple 1 expression for revenue and 2'nd one for %?

regards

Darek

Re: Table with multiple expression and two columns of data

Mon, 14 Apr 2014 11:27:07 GMT

A unique expression for each category. So there would be 10 unique expressions in the table above

Re: Re: Table with multiple expression and two columns of data

Mon, 14 Apr 2014 11:37:40 GMT

Re: Re: Table with multiple expression and two columns of data

Mon, 14 Apr 2014 11:45:02 GMT

Are you essentially saying I need to build a table in the background with the expressions? And then reference that table? I'm not sure how this would work seeing as the calculated values must be dynamic and change when the data range is altered

Re: Re: Table with multiple expression and two columns of data

Mon, 14 Apr 2014 11:53:18 GMT

Alex,

please give me some more clear example what you need. Maybe show me fex cases in excel....

In my exaple there is simple way how you may have different expressions for 1 KPI for each category.

But i'am sitll not sure if you really need this.....

regards

Darek

Re: Table with multiple expression and two columns of data

Mon, 14 Apr 2014 13:20:44 GMT

OK, i think i know what you mean.

However, you have defined a category table. If I am to use this method, do I need to define such as table as well?

Re: Table with multiple expression and two columns of data

Mon, 14 Apr 2014 13:45:30 GMT

Alex,

it is rather no common need, that you want different formulas for each dimension value.

Considering, that you have fact table for example with your sales and category is for example you product category, you should have the same formula for each category. To achieve this you have to do some data transformations in your load script.

Of course, there may be cases, when your "category" is rather KPI name, than business dimension, and then you may need to use different formulas for each of them.

If you have this case, you need pick(nbr,expr_1,expr_2,....,expr_n) eventually if(category='A',sum(value),avg(value)). But personally i prefer pick()

If you want, you may give me more detailed info why you need those different formulas and i will try to reccomend good solution for you.

regards

Darek