Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple expressions in an expression field in a pivot table

Hello,

I would like to create a pivot table like this

KPICurrent yearLast yearGrow%
Total sales120.000100.00020%
Total toy sales40.00050.000-20%
Number of customers13.00010.00030%
Average sale per customer 9,210-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

8 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

Hi,

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

Regards,

jeffmartins
Partner - Creator II
Partner - Creator II

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

maksim_senin
Partner - Creator III
Partner - Creator III

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',

          1,

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

='Total toy sales',

          2,

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

='Number of customers',

          3,

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

='Average sale per customer',

          4,

))))

You'll have:

1.png

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

somenathroy
Creator III
Creator III

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

Not applicable
Author

Thanks a lot. It works!

Not applicable
Author

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

Not applicable
Author

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