I've using Data Manager created a DMR fact table.
To simplify let's take this example Dimension Product and Dimension Time, Measure as planned sales per month
I need to be able to compare month planed sales, with those planned last month, in one crosstab
So I created a table as
ProductID,MonthID, Value, Capture_Date
101, 201603, 25, '2016-08-09 17:06:23.000'
101, 201603, 15, '2016-08-31 06:36:52.000'
1) Crosstab with one query doesn't work , shows for both capture_date 40
2) union of two queries. Works in View tabular data, but in crosstab ==> RQP-DEF-0502 Master dataset can not be used for the provided query
to 2) found on the net that union/DMR/Crosstab will not work ?!
How can I achieve what I want?
Thank you
Try a case statement for each measure, in the same query:
Last Month Planned Sales (Pseudo Code)
CASE WHEN CURRENT_MONTH
THEN 0
ELSE PLANNED_SALES
END
Current Month Planned Sales (Pseudo Code)
CASE WHEN LAST_MONTH
THEN 0
ELSE PLANNED_SALES
END
The aggregation I assume is Total.
What we're doing is we're substituting a 0 for the measure when it's not for the month that you want. When the rollup occurs, you ensure that you're only adding values from last month or current month, depending on the calculation.