How to have 1 date dimensions when you use set analysis?

[tinkerz1]

Hi,

I have written a set analysis expression that pulls the right set of data, but because the 'Month_Logged_Open_Balance' is not a dimension in the straight table, 'Master Month Date ' is used, what happens is an aggregation of numbers.

How do I link 'Month Logged Open Balance' to 'Master Month Date' in the dimension?

Here is an example:

In the above example I need the sets:

Month = 30/11/2016 and Month_Logged_Open_Balance=30/11/2016 (2152)

and

Month = 30/12/2016 and Month_Logged_Open_Balance=30/12/2016 (3310)

When the Month_Logged_Open_Balance dimension is taken out and with a distinct count the figures understabely change.

I don't know how to keep the Master Month statement as the dimension and get the numbers from Month_Logged_Open_Balance to be correct, or what I need too see, I have other expressions that use Master Month Date, they are switched off at the moment.

my formula is:

count(DISTINCT{$<[MonthLogged_Open_Balance]=$::[Master Month Date],LOBMapping_Open_Balance=$::LOBMapping,CountryID_Open_Balance=$::CountryID,BusinessArea=$::ReMappedBusinessArea>} CaseIDR5_Open_Balance)

Thanks

Neil

[sunny_talwar · Accepted solution]

May be this:

Aggr(If(MONTH = Month_Logged_Open_Balance, count(DISTINCT{$<[MonthLogged_Open_Balance]=$::[Master Month Date],LOBMapping_Open_Balance=$::LOBMapping,CountryID_Open_Balance=$::CountryID,BusinessArea=$::ReMappedBusinessArea>}CaseIDR5_Open_Balance), Month_Logged_Open_Balance, MONTH)

[sunny_talwar]

Or if you have a field which uniquely define each combination of MONTH and Month_Logged_Open_Balance, then you can use set analysis like this:

Count(DISTINCT{$<[MonthLogged_Open_Balance]=$::[Master Month Date],LOBMapping_Open_Balance=$::LOBMapping,CountryID_Open_Balance=$::CountryID,BusinessArea=$::ReMappedBusinessArea, UniqueField = {"=MONTH = Month_Logged_Open_Balance"}>}CaseIDR5_Open_Balance)