Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
It may be helpful if you can provide a sample set of data.
Regards,
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
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:
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
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
Thanks a lot. It works!
Thank you for your offer to help. As you can see above Michael gave me a solution. Regards.
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