Hi All
I have a Transaction Table which has Sales Amount.
I have another Table called Calendar Table Which shows Calendar Details as per Company defined Dates.
Eg. For January, it shows - 1 Jan - 27 Jan,
For February - 28 Jan - 26 Feb,
For March - 27 Feb - 26 March,
& For April - 27 March - 28 April, and so on.
I have added this Calendar Table in Transaction Table based on the Month field Key.
From the Calendar Table i am showing Max Month only.
But there is an issue which i am facing:
As per the my Scenario, the Calendar is showing only April Month which has started today only.
In the staring of the Month some Zones had made no Sales or Business.
Eg. Today the Date is 27 March 2014, as per Calendar this will come in April Month.
Suppose There are 4 Zones: North, South, East & West.
Sales made by Zones on 27 March 2014, North - 7 Lacs, South - 5 Lacs, East - O & West - O
For Max Year, i have use variable = Max(Year), &
For Max Month, i have used variable = only(CalendarTableMonth) Since this table has only 1 value so it shows me April.
I have used Set Analysis which give me different Zone Sales by picking Max Month & Max Year
The Sales is showing perfectly for North & South Zones since the Transaction Table & Calendar is joined on the basis of Month field and their April Month is Generated in Transaction Table.
The East & West cases are not appearing in the chart because their sales have not been made in April Month and their April Month is not Generated in Transaction Table so, it is not showing up.
But i want if the Zones does not make sales it should show 0.
I am getting this only when i change my Max Month expression and doing it Hard Coded as 'Apr' than it will give me Perfect Result as:
North: 7 Lacs
South: 5 Lacs
East: 0
West: 0
But, the Problem is that i don't want to do Hard Code. I want the system to do this automatically.
Is there any Workaround for this.....??
Regards
Aviral Nag
