Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to show in a graph the % of total order intake by month (for any selected dimension - in this case ord type) in a graph that show several years by month.
I currently use this expression:
Sum({<item_type ={1}>} quantity_ordered)/Sum(TOTAL {<item_type ={1}>} quantity_ordered)
but obviously it show the % based on the Total number of units (all months) instead of the isolated month. I would like to see a total of 100% for each month.
Expression for "Ord Yr/Month" as per below (if it helps you to solve this matter...)
=YEAR (order_date) & Month (order_date)
Is there an easy formula to get this to work?
Current graph below:
BR & thanx in advance Peter
You need to use your "Ord Yr/Month" dimension within the total qualifier:
Sum({<item_type ={1}>} quantity_ordered)/Sum(TOTAL <[Ord Yr/Month]> {<item_type ={1}>} quantity_ordered)
The problem is that it is a calculated dimension and it won't work. You just need to create it in you data during load; something like:
YEAR (order_date) & Month (order_date) as [Ord Yr/Month],
Thanx! will try that. BR Peter
Hi again, partly solved 🙂
But I cant get the OrdYrMonth dimension showing the year+month, now it is 0-12 where the 0 is probably the total of full year (pic1), is the only way to use an expression to solve this?
If I select one individual year such as 2020 (selected on original "order_date") I get only the total (pic2) - should 3 months
If I select one individual year such as 2019 (selected on original "order_date" I get 3 months + total .. (pic3) -
Load script used now:
YEAR (order_date) & Month (order_date) as "OrdYrMonth",
Expression used:
Sum({<item_type ={1}>} quantity_ordered)/Sum(TOTAL <[OrdYrMonth]> {<item_type ={1}>} quantity_ordered)
Any ideas whats gone wrong?
BR Peter
pic1:
pic2:
pic3:
Strange
Can you just create a table with 4 dimensions to see what's going on?
OrdYrMonth
order_date
YEAR (order_date)
Month (order_date)
Exactly what I did! and now it is ok 🙂
Thanx for everything