Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
See why Qlik was recognized for the seventh year in a row – and discover how we can help you tackle your data integration challenges. Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
tinkerz1
Creator II
Creator II

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

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:

Mixed dimension.PNG

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.

Mixed dimension 2.PNG

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

1 Solution

Accepted Solutions
sunny_talwar

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)

View solution in original post

2 Replies
sunny_talwar

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)