Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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)