Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

rk_jnj_horsham
New Contributor

Does QLIK have analytic functions to enable complex in-memory data selection and aggregate analysis?

I have an application scenario for which set analysis appears inadequate for providing a solution for data aggregation (e.g., Count).

In my dataset, I have discrete subgroups (subpopulations) within which the members have attributes that vary over time. I need to select (and count) the number of unique subgroups for which only a single member (within each subgroup) has the desired attribute within a time period of interest. However, the value for the desired attribute will differ between subgroups within a given time period and will also differ within a subgroup when compared at different time periods. Therefore, filtering the entire population by a single attribute value, including a single function-based expression (e.g., Min, Max), or by a set of values prior to calculating an aggregate statistic (e.g., count) will not produce a correct result.

For example. For a population comprising time periods T1, T2, and T3; subgroups S1, S2, S3, S4, and S5; and subgroup members with numeric attributes A1..A100, I need to count the number of unique subgroups within period T2 after first identifying (i.e., filtering/selecting for) a single member within each subgroup that has the maximum attribute value. NOTE: In reality, the number of subgroups varies between time periods and may be very large within a given time period.

*In addition, the selected member within each subgroup will need to be presented within a QLIK table visualization, which will include other member attributes.

So, for the following population, the count of unique subgroups in T2 is 3 (i.e., S2, S3, and S4), with selected members: S2-A3, S3-A6, and S4-A1.

How can I build a function in QLIK to obtain this number (and select the correct members within the QLIK table)?

T1: S1-A3, S1-A4, S1-A5; S2-A1; S3-A2

T2: S2-A2, S2-A3; S3-A3, S3-A4, S3-A5, S3-A6; S4-A1

T3: S3-A7, S4-A2, S4-A3, S5-A1, S5-A2, S5-A3

1 Solution

Accepted Solutions
MVP
MVP

Re: Does QLIK have analytic functions to enable complex in-memory data selection and aggregate analysis?

Should be straightforward - or perhaps I am missing some important point. Its hard to give a specific example as I have no information on the data structure in your model. If it is well structured for this analysis, it could be as simple as

     Dimension; TimePeriod

     Expression; Sum(DISTINCT SubGroup)

This sounds like a slowly changing dimension problem. Check out here:Google - Slowly Changing Dimensions and search this site for the same.

2 Replies
MVP
MVP

Re: Does QLIK have analytic functions to enable complex in-memory data selection and aggregate analysis?

Should be straightforward - or perhaps I am missing some important point. Its hard to give a specific example as I have no information on the data structure in your model. If it is well structured for this analysis, it could be as simple as

     Dimension; TimePeriod

     Expression; Sum(DISTINCT SubGroup)

This sounds like a slowly changing dimension problem. Check out here:Google - Slowly Changing Dimensions and search this site for the same.

rk_jnj_horsham
New Contributor

Re: Does QLIK have analytic functions to enable complex in-memory data selection and aggregate analysis?

Jonathan,

Thank you for your reply and suggested reading!

The underlying data can be categorized as a Type 2 Slowly Changing Dimension – so now I have a name for this malady!

Your suggested expression will indeed correctly count the number of unique subgroups within a filtered time period. Thank you.

The more perplexing issue is selecting the correct member from within the subgroup. David McMahon from QLIK will be helping me with this.

Thanks again!

Community Browser