Ignore Dimension Set analysis?

[FrankC]

I am trying to create a pivot table that shows current value and a previous value.  My metrics work fine if the transaction date is not displayed in the table.  However, I need to show the data at a date level

TransDate Group Product (all dimensions) ----->  Actual (based on transdate)   Previous (based on transdate -1)

I can only display Actual or Previous.  But not both.  The only way both are displayed is if Transdate is removed from the pivot table.

Actual: Sum({<MetricType ={'Actual'}, MetricClass={'GL'},DateNum = {">=$(vMinTxnDate)<=$(vMaxTxnDate)"}>}Value)

Previous: Sum({<MetricType ={'Actual'}, MetricClass={'GL'},DateNum = {">=$(vMinTxnDate-PP)<=$(vMaxTxnDate-PP)"}>}Value)

There join between the calendar and fact is on TransactionDate.

I have added various combinations to ignore selections, but when I do only Previous contains values.  Without added ignore selections, Actual has values.

[marcus_sommer]

Logically it's excluding each other. Principally there are ways to force foreign values to a dimension-value but IMO it's not recommended.

Within the most scenarios it's fine and expedient to use multiple expressions without those period-dimension to get the wanted views. If it should be really displayed against a period-dimension I would use an appropriate prepared one. This might be the days of a month or the calendar-dates without the year-information because the current data and previous ones exists against these dimensionality.

- Marcus

[FrankC]

Marcus

Thanks for your confirmation.  I do have other tables that provide current and previous on the same row, but date in not included but is a filter instead.

[marcus_sommer · Accepted solution]

It would be the same. If these dimension is shown within an object or used on the outside as as filter is the same - means you need additionally fields to your "normal" period-fields which are connected to a complete date - unless the max(date) which comes from the user-selection is used to define the periods of the current and previous dates (probably this what your date-variables from example above does).

- Marcus