Hi,
I have a webi issue where we have 2 measures. Shipped tons and Projected tons.
I have last 12 months Data coming from Cal year.Month field from Bex.
In the Report I have to show Data for all previous 11 months for Shipped Tons but for current month need to show the Projected Tons.
For example, in cross tab report:
Mar 2016 Apr 2016 May 2016 Jun 2016
Material A 50 35 40 25
Material B 41 22 23 15
In Report need to show Shipped Tons from Mar to May and for Jun 2016 , show different measure Projected Tons.
Issue is that with adding another measure the columns are doubled for every month. Need help… thanks
This is possible in a single cross tab. You just need to capture the latest month in a variable(V_LatestMonth) and then use that variable to display data accordingly. I have taken an example. I have a cross tab in which I am having “Location” & “Months” as Row & Column respectively. I also have two different measures “Acc. Amount” and “Ret. Amount”. I will display Acc. Amount for all the months except the last month. For last month I’ll display Ret. amount.
The variable definition of V_LatestMonth=Month(ToDate((Max([Month]) IN REPORT);“MM”))
Now please follow the snapshot attached. The first table in snapshot displays Acc. amount and second displays Ret. amount. The third table output is what you are looking for. In third table all the months have Acc. amount except the latest month (has Ret. amount).
Hi,
Thanks for the help.
I’m sure your solution should work, only thing is that at first step I am having issues to get the max Month.
basically my data is from BW, so 0CALMONTH (cal.year/month) , when I try the following formula it is failing:
=FormatDate(ToDate((Max([Cal. year / month].[Cal. year / month - Key]) In Report);"MM-yyyy");"Mmm-yyyy")
Any solution to this. It has taken an awful lot of time for me to get the max month of this [Cal.year/month] … data is 02/2016 format but as BW data it’s data type is not Date.
Any ideas on this …
(BOB member since 2009-12-31)
(BOB member since 2009-12-31)