hello ppl,
i need the following:
i got a report with a cross table in it the measure is the total calls(ppl called).
the collums are the months and the rows are the years so u get for example
(calls) jan
2014 20
2013 30
now i want a row under the line 2013 that shows me the differance between 2014 en 2013.
so i want it to show 10
witch function do i use for this of how can i make it so i can see the differance.
tnx for the help !
Is this a relational or dimensional data source?
relational!
There are a few ways of doing it.
Let's try the easy way:
Create two data items:
Current Year: case [NS].[Time Dim].[Year] when #timestampMask($current_timestamp,'yyyy')# then [Measure] else 0 end
Previous Year: case [NS].[Time Dim].[Year] when #timestampMask(_add_years($current_timestamp,-1),'yyyy')# then [Measure] else 0 end
Create a third data item:
[Current Year] - [Previous Year]
Put that data item in your crosstab and, hopefully, it should be what you're expecting.
after some trys i got to work with a part of your awnser
datepart 1(2014):
IF ([jaar]= 2014) THEN ([aantal]) ELSE ('0')
datepart 2(2013):
IF ([jaar]= 2013) THEN ([aantal]) ELSE ('0')
datepart 3 (differance):
[2014] - [2013]
but now if i look at the pivot table there is a differance of 1 per table the differance between the tables stays the same see below
jan but it shows
2013 5 6
2014 6 7
differance 1 1
how can the output of 2013 and 2014 be the anwser +1 and how can i Ensure that the calculation is correct?
Is the aggregation method count or count distinct? use ELSE (null) instead.