Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks, I am trying to create a table to Show, Shipments at SOM (Starting on Month), Shipments Started, Shipments Closed, Shipments at EOM (End of Months) for each MonthName. Please find the below sample data:
SHIPMENT_ID, STATUS, DATEVALUE
1000, Started, 5/25/2013
1000, Closed, 5/30/2013
1001, Started, 5/25/2013
1001, Closed, 6/2/2013
1002, Started, 5/29/2013
1002, Closed, 6/5/2013
1003, Started, 5/20/2013
1003, Closed, 5/26/2013
1004, Started, 6/2/2013
1004, Closed, 6/10/2013
1005, Started, 6/15/2013
1005, Closed, 6/22/2013
1006, Started, 6/23/2013
1006, Started, 6/22/2013
I am expecting the Table (Out Put) like below:
MonthName Shipments (SOM) Shipments Started Shipments Closed Shipments (EOM)
May2013 0 4 3 2
June2013 2 4 4
Shipments (SOM) --> # of Open Shipments at Starting of Month
Shipments Started --> # of Shipments Started Count({<STATUS={'Started'}>}SHIPMENT_ID)
Shipments Closed --> # of Shipments Closed Count({<STATUS={'Closed'}>}SHIPMENT_ID)
Shipments (EOM) --. # of Open Shipments at End of Month
Please help me how to calculate Shipments (SOM) & Shipments (EOM)??
Appreciated for any help!....
Any thoughts ??
See this blog post: Creating Reference Dates for Intervals. It's the same problem, except with policies instead of shipments.
Thanks Gysbert. I checked the blog and done the same data model and I didn't get the SOM & EOM values when clear the slections.
Please check the attched file. Is there any wrong with my set expression?
One more, How can I calucluate the Shipment Started & Closed on that Month??
Thanks in Advance..
Any thoughts?
To begin with your data contains errors. There are Open_Date values like 30/1/2012 with MNVALUE Feb 2012. That's not correct. Secondly you can't use set analysis in this case since you use MNVALUE as a dimension. So you need to use if statements. See attached qvw.
edit: The sets for the set analysis expressions are calculated once for the entire chart, not per row. That's why you often can't use the same field as a chart dimension and as a set modifier.
Thanks Gysbert,
I have one question. why set expression is not working on MNVALUE as dimesion. But I still get the min and max values in the expression while MNVALUE as dimension?