topic Re: Multiple expressions in an expression field in a pivot table in QlikView

Multiple expressions in an expression field in a pivot table

Thu, 21 Mar 2013 13:28:25 GMT

Hello,

I would like to create a pivot table like this

KPI	Current year	Last year	Grow%
Total sales	120.000	100.000	20%
Total toy sales	40.000	50.000	-20%
Number of customers	13.000	10.000	30%
Average sale per customer	9,2	10	-8%

I created a dimension like this:

=valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

That works.

But how do I get the expression fields filled? I was thinking of building the expressions Current Year and Last Year each containing 4 expressions. But how do I formulate this?

Or is there a better solution?

Thanks for thinking with me.

Tariaj

Re: Multiple expressions in an expression field in a pivot table

Anonymous — Thu, 21 Mar 2013 16:01:31 GMT

I'd rather use straight table here. No much difference in this case. As for expressions, the "Current Year" may look like this:

if(valuelist(...)='Total sales', <expression for total sales curr year>,
if(valuelist(...)='Total t sales', <expression for total t sales curr year>,
if(valuelist(...)='Number of customers', <expression for number of customers curr year>,
if(valuelist(...)='Average sale per customers', <expression for avg sale per customer curr year>
))))

Similar expression for the last year.

the third column can be calculated using the prevous two, e.g.
([Current Year] - [Last Year])/[Last Year]

(where Current Year and Last Year are the labels of the first two expressions)

Regards,
Michael

Re: Multiple expressions in an expression field in a pivot table

Thu, 21 Mar 2013 22:51:57 GMT

Hi,

It may be helpful if you can provide a sample set of data.

Regards,

Re: Multiple expressions in an expression field in a pivot table

jeffmartins — Thu, 21 Mar 2013 23:37:58 GMT

Hi tariaj68,

You can create different expressions for each line using the pick function. For each line you should define a specific set analysis expression.

pick( RowNo(),

sum(Total Sales),

sum({< Toy = {1} >}Sales),

Count(Distinct Customer),

avg(Sales)

)

You dimension is correct as valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

Hope this helps you.

Regards

Re: Multiple expressions in an expression field in a pivot table

maksim_senin — Fri, 22 Mar 2013 04:17:26 GMT

Hi,

That really depends on what you have in your data. Anyway it's better if you prepare you data for calculation, i.e. have a KPI dimension and values calculated in a load script otherwise you risk to have performance issues (highly significant for huge volumes) and need to think out magic formulas.

With the calculated dimension you can use the following expression:

if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

='Total sales',

if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

='Total toy sales',

if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

='Number of customers',

if(valuelist('Total sales','Total toy sales','Number of customers','Average sale per customer')

='Average sale per customer',

))))

You'll have:

Just paste your formulas instead of 1-4, don't forget to add set analisys for LFL comparison. But it's better to think about (re-)designing of data.

Best regards,

Maxim

Re: Multiple expressions in an expression field in a pivot table

somenathroy — Fri, 22 Mar 2013 06:31:18 GMT

Hi,

You may create a Inline table like:

Load * inline

[KPIID,KPIMatrix

1,'Total Sales'

2,'Total toy sales'

3,'Number of customers'

4,'Average sale per customer'];

Add KPIMatrix in dimention list

In chart expression:

Exps1(CurrentYear):

If(KPIMatrix = 'Total Sales', Expr_for_TotalSales<CurrentYear>,

If(KPIMatrix = 'Total toy sales', Expr_for_Total_ToySales<CurrentYear>,

If(KPIMatrix = 'Number of customers', Expr_for_NumberOfCustomer<CurrentYear>,

Expr_for_AvgSalePerCustomer<CurrentYear>

)

Exps2(Last Year):

If(KPIMatrix = 'Total Sales', Expr_for_TotalSales<LastYear>,

If(KPIMatrix = 'Total toy sales', Expr_for_Total_ToySales<LastYear>,

If(KPIMatrix = 'Number of customers', Expr_for_NumberOfCustomer<LastYear>,

Expr_for_AvgSalePerCustomer<LastYear>

)

Exps3(Growth%):

(Exps1-Exps2) / Exps1

Regards,

Som

Re: Multiple expressions in an expression field in a pivot table

Fri, 22 Mar 2013 14:56:34 GMT

Thanks a lot. It works!

Re: Multiple expressions in an expression field in a pivot table

Fri, 22 Mar 2013 14:57:49 GMT

Thank you for your offer to help. As you can see above Michael gave me a solution. Regards.

Re: Multiple expressions in an expression field in a pivot table

Wed, 27 Mar 2013 09:45:57 GMT

Thank you all for your helpfull replies.

I tried them all and they all work.

Finally I applied the solution of Jeff Martins using the pick( RowNo()) because in that way the expressions were the shortest and I had the feeling the performance also was the best.

I am thinking of moving some expressions to the load script as Maxim Senin recommended.

Regards,

Tariaj