0

2nd Level aggregate on specific groups

I have a requirement which needs aggregation to be applied, on aggregated data, on specific groups. Something similar to the pseudo-SQL statement below.

SELECT Group1, COUNT(Group2), COUNT(condition which filters certain Group2s)

FROM (SELECT Group1, Group2, Group3, COUNT(Attribute)

                  FROM Table

                  GROUP BY Group1, Group2, Group3)

GROUP BY Group1

Further description on the need:

PFA. Consider data in Granular Data table.

Based on that data, I would like to find number of users per region whose order count equals corresponding region's order count.

Conditions:

1) This column should be reactive to dashboard filters. So, this should be calculated dynamically as an insight formula column.

2) Column level filter should not be used, as this is just a part of the formula and other part needs total rows information.

 

I have been trying to achieve this for a while. It would be really helpful, if I get a clue.

Thanks in advance.

8replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • You can create a Incorta table on the first agg. query and then create a dashboard on top of that table .

    Reply Like
    • Amit Kothari If I do so, aggregated column values will be hard-coded. I want it to be reactive to dashboard filters.

      Reply Like
    • Naresh Kumar I meant the inner group by should be a Incorta table -  

      FROM (SELECT Group1, Group2, Group3, COUNT(Attribute)

                        FROM Table

                        GROUP BY Group1, Group2, Group3)

      Reply Like
    • Amit Kothari This was instinctive approach for me too. I created an incorta view for 1st level aggregation and tried to do number of users calculation in insight. I'm stuck in this step - distinct(if(sum(ordercountperregionuser, groupBy(regionid, userid)) = sum(sum(ordercountperregionuser, groupBy(regionid)), userid, NULL)regionid)

      But, I need a NULL equivalent in incorta, as incorta doesn't support NULL.

       

      I tried using null(). But, it didn't give expected results.

      distinct(if(sum(ordercountperregionuser, groupBy(regionid, userid)) = sum(sum(ordercountperregionuser, groupBy(regionid)), userid, null())regionid)

       

      I didn't try using an external variable having NULL value.

      distinct(if(sum(ordercountperregionuser, groupBy(regionid, userid)) = sum(sum(ordercountperregionuser, groupBy(regionid)), userid, $Null)regionid)

      External variable $Null value - SELECT NULL

      External source - SQL server 

      Does this approach work?

       

      Thanks in advance.

      Reply Like
    • Naresh Kumar  refer to this for how  to avoid counting nulls in a distinct function  - https://community.incorta.com/t/y7js4h/how-to-avoid-counting-nulls-in-a-distinct-expression

      Reply Like
  • Our Incorta views feature coming in Fall 2019 will address this use case and you can have a similar experience to the Incorta table but without having to materialize it at the load time, it will also take into account any dashboard filters, etc you might have applied so it will be dynamic. 

    Reply Like
Like Follow
  • 2 wk agoLast active
  • 8Replies
  • 41Views
  • 3 Following