Hello,
i have crosstab with measure in the corner, product in rows and 12 months with actual value and planing value nested under the month.
The planing value show from januar (01) till december (12). But I will show only the acutal value from begining of the year to current month. Jan (01) - May (05).
How can I filter only the acutal value so that it show the value till may, and the planing value dont filter for all year?
can anyone help me?
Im using DRM.
thnx very much
I can think of a few ways of handling this, but first I need a better understanding of your data.
Do you have actual data for the future? How does it appear at the moment?
Assuming that the problem is that you have future data, the easiest way might be to simply use an if statement. In Framework, add "Month Number" as an attribute of the month level, then you could do something like:
if([Namespace].[Time Dim].[Time Hier].[Month].[Month Number] <= #timestampMask($current_timestamp,'mm')#) then ([Namespace].[Measures].[Actual]) else (null)
Hello CognosPaul,
thank you for your reply.
I have a dateelement with two scenario (actual and planing).The actual is the real values growing with month and the planing is fix forecast value for all of year. Take look at a Picture.
Have try it with details filter, but it has boolen error.
if ([Dimensionales Reporting].[Zeit].[Zeit].[Monate].[Monat Lfd. Nummer]<= #timestampMask (_add_months($current_timestamp,-1);'mm')#) then ([Ist]) else (0)
or with fix month:
if ([Dimensionales Reporting].[Zeit].[Zeit].[Monate].[Monat Lfd. Nummer] 5<= then ([Ist]) else (0)
By "forrecast planing value" I want all year value, and "is-current value" only the closing period 1,2,3,4.
How can I remove boolen error?
or perhaps with other way "closingperiod or periodtoday(currentmonths;-1)"?
thank you for your help
Based on the image, you shouldn't need a detail filter at all.
Would your data ever legitimately contain a zero value? If not, why not set the zero character to a space using the data format?
There are a few directions I can see going here. Since the data is actually correct, let's try to remove the value from the layout. We can use a variable to do that. Create a Boolean variable with the following expression
([Szenario Schlüssel] = 2) or ([Szenario Schlüssel] = 1 and [Monat Lfd. Nummer] < extract(month,Today()))
Unlock the report, and set that as a render variable on the text item in the intersections (the <#1234#>)
Hello CognosPaul,
I understand the solution and it work with a fix month on the crosstab,
([Abfrage1].[Szenario Schlüssel]=2) or ([Abfrage1].[Szenario Schlüssel] = 1 and [Abfrage1].[Monat Lfd. Nummer] <3)
but with this it has error, the extract(month;current_date , Today seem don't work here.
([Abfrage1].[Szenario Schlüssel]=2) or ([Abfrage1].[Szenario Schlüssel] = 1 and [Abfrage1].[Monat Lfd. Nummer] <extract(month;current_date))
([Abfrage1].[Szenario Schlüssel]=2) or ([Abfrage1].[Szenario Schlüssel] = 1 and [Abfrage1].[Monat Lfd. Nummer] <extract(month;current_date()))
([Abfrage1].[Szenario Schlüssel]=2) or ([Abfrage1].[Szenario Schlüssel] = 1 and [Abfrage1].[Monat Lfd. Nummer] <extract(month;today()))
Another question. I have a chart how can I do it as same as the crosstab?
thank you
Use: extract('month',date2timestamp(Today())
A chart is different than a crosstab in that we actually do need to change the value in the query. In this case, instead of using a single measure against the scenario set, we'll tuple the measure.
So create two data items:
if([Monat Lfd. Nummer] < #timestampMask($current_timestamp,'mm')#) then (tuple([Measure],[Szenario 1])) else (null)
and
tuple([Measure],[Szenario 2])
Drag those into the series of the chart.