Hi
I have a requirement to create percent of total for one of the column.
I have created couple of logical columns from Order Date and Ship Date in RPD i.e.,
1. Days to Ship Actual - TimeStampDiff(SQL_TSI_DAY, "ORDERS"."ORDER_DATE" , "ORDERS"."SHIP_DATE" ) // Which gives No of Days information
- Days to Ship scheduled - Which gives information about what to happen if there is a match in no. of days. Ship mode column contains data like Same Day, First class, etc. which is given below in the case statement
CASE "ORDERS"."SHIP_MODE"
WHEN 'Same Day' THEN 0
WHEN 'First Class' THEN 1
WHEN 'Second Class' THEN 3
WHEN 'Standard Class' THEN 6
END
3. Ship Status - Gives information about what to display in regard the ship status
if [Days to Ship Actual]> [Days to Ship Scheduled] then "Shipped Late"
elseIF [Days to Ship Actual]= [Days to Ship Scheduled] then "Shipped On Time"
Else "Shipped Early" end
Now the requirement is I want to calculate the percentage of total of Ship status column
Means I wanted to know what is the Contribution of each status like
What is the percentage of shipped Late, Shipped early and On time? in a single column.
I have used the below case statement for the requirement
CASE "D1_ORDERS"."Ship Status" WHEN 'Shipped Early' THEN ((1.0*SUM(CASE "D1_ORDERS"."Ship Status" WHEN 'Shipped Early' THEN 1 ELSE 0 END))/(1.0*COUNT("D1_ORDERS"."Ship Status")))*100 WHEN 'Shipped Late' THEN ((1.0*SUM(CASE "D1_ORDERS"."Ship Status" WHEN 'Shipped Late' THEN 1 ELSE 0 END))/(1.0*COUNT("D1_ORDERS"."Ship Status")))*100 WHEN 'Shipped on time' THEN ((1.0*SUM(CASE "D1_ORDERS"."Ship Status" WHEN 'Shipped on time' THEN 1 ELSE 0 END))/(1.0*COUNT("D1_ORDERS"."Ship Status")))*100 ELSE 0 END
But the problem with this is when I add Ship status dimension column in the table everything coming as 100%
With out dimension column it works perfect as expected
I am trying out to figure out this, but unable to achieve it. Please help me to fix the requirement.
Thank you