2 Replies Latest reply: Dec 2, 2016 1:37 PM by Robert Kwarta Jr RSS

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

    Robert Kwarta Jr

      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