No of Shipments at Starting and Ending of the month

[#1]

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!....

[#2]

Any thoughts ??

[Gysbert_Wassenaar]

See this blog post: Creating Reference Dates for Intervals. It's the same problem, except with policies instead of shipments.

[#4]

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..

[#5]

Any thoughts?

[Gysbert_Wassenaar]

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.

[#7]

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?