Current Month (Count of Distinct(Part No)) VS Previous 6 Months (Count of Distinct(Part No))

[#1]

Dear All,

Please provide solution for the below requirement.

I need to create a BAR graph with two expression's. In this there is no dimension field. Only with expression's i need to create.

1) First expression is to create current month(count of distinct(Part No)).  Here Date Field is "Order Date".

2) Second expression is to create previous 6 month's (count of distinct(Part No)). Here also same Order Date range I am using.

Note :- User has an selecion option of ORDER DATE  too. i.e. LIST BOX (ORDER DATE).

Eg :-    Scenario 1) By default current month (MAR- 2014) and Previous 6 months (SEP-2013 to FEB-2014) data should show in bar graph.

As I told, User have a selection option of Order Date. For Eg :- 

Scenario 2) If User selects order year and month as (JAN-2014) then Graph should be as follows.

One BAR should be (JAN-2014) Data and another BAR graph should be previous six months data i.e. (JULY-2013 to DEC-2013).

With my expression's I am able to show default case (scenario 1), But when I selecting any other order month, NULL values are coming.

Hope you understand my requirement. Please provide me sloution for the same. I have to deliver this report asap. Thanks in advance.

[#2]

can u provie sample

[MK_QSL]

If you could share your sample apps or data it would be easy to answer you.

If you have created master calender... Add one more Preceding Load just above the calendar table.

Load * , AutoNumber(MonthYear, 'MonthYear') as MonthYearID;

Now create below two expressions.

COUNT(Distinct {<MonthYearID = {'$(=Max(MonthYearID))'}>} [PART NO])

COUNT(Distinct {<MonthYearID = {'$(=Max(MonthYearID)-6)'}>}[PART NO])

[#4]

Dear All,

Please find the attached Sample data for your reference.

in our application we have not created any Master Calendar as that can't be used here.

Thnks..

[MK_QSL]

Check enclosed file...

[#6]

Thanks for your support.

I checked your application. But my requirement is to get the previous months data also if user selects the order date.

In your application which you shared me, please select the YEAR = 2014, MONTH = Jan and check the bar graph. Its showing only one bar which the selected month data which is corrrect but i requirement is one more bar with previous 6 months data ie.e (2013- July to 2013 -Dec). Hope you got my point.

Please let me know. Thank you.

[MK_QSL]

Use this...

Instead of year and month why don't you select MonthYear Directly.

[MayilVahanan]

Hi

Try like this

=COUNT({<OrderDate= {">=$(=Date(MonthStart(Max(OrderDate),-6),'DD/MM/YYYY'))<=$(=Date(MonthEnd(Max(OrderDate),-1),'DD/MM/YYYY'))"}, Year = , Month = , MonthYear = >} Distinct [PART NO])

-- Previous 6 month

Edit:

=COUNT({<OrderDate= {">=$(=Date(MonthStart(Max(OrderDate),0),'DD/MM/YYYY'))<=$(=Date(MonthEnd(Max(OrderDate),0),'DD/MM/YYYY'))"}, Year = , Month = , MonthYear = >} Distinct [PART NO])

-- For Current Month