0

Map Chart (with Incorta map) showing % of total

I am trying to use a map chart at the state level. I can show a simple aggregation by state with a count of accounts. What I would like to do is show the percent of the total population for each state rather than the count. I thought using a formula could work with count() and groupBy() but the state dimension seems to override the total population count. The formula was like this:

count(acctId,groupBy(statecd))/count(acctId)

The result is always 1 since the group dimension on the map chart is statecd.

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Marc, Can you try this:

    count(acctId,groupBy())/count(acctId)
    Like
    • Anurag Malik, I tried that and the quotient is not right.  When I use an aggregated table, I used the base field with COUNT as the dividend and the base field with COUNT and a state filter equal to AZ as the divisor and a thrid formula (shown below) for the quotient. These work as expected.

      The actual numbers are:

      2,880,437

      83,429

      2.90%

      sum(if(dwstar.Customers.State = "AZ", 1, 0)) / count(dwstar.Customers.DebtorID)

      The Customers table has the ISO state code.

      Side question, how is the County valued for the map? What value in my data is needed to resolve the map to the county level?

      Like
  • Hi Marc,

    My bad. If you have state as a dimension, then the formula above should have been:

    count(acctId) / count(acctId,groupBy())

    This video shows how to use similar formula:

    https://www.youtube.com/watch?v=xSNIsddTj4E

     

    To show data by county, you need to set map by county and use county as a dimension.

    Like 1
    • Anurag Malik This is great! Thank you very much. 

      As for the county, what is the value that is compared at the county level in the Incorta Map?

      Like
    • Marc Paige We have introduced advanced map in 4.9 where you many options to show data at county level.

      Do you have access to 4.9?

      Like
    • Anurag Malik Not yet. I would love to have it early.

      btw, I went old school on this and created two MVs that provided the totals and by state totals. I had not known about the scale of percentage before your video. Such a simple thing but so powerful!

      Like
Like Follow
  • Status Answered
  • 2 wk agoLast active
  • 6Replies
  • 19Views
  • 2 Following

Product Announcement

Incorta 4.9 is now Generally Available (GA)!!!