Pie chart with derived categories

Comments

7 comments

  • Avatar
    Adi Hecht

    Not quite sure I understand what you are trying to achieve, but it looks to me that if in your elasticube you have a custom table where the columns are "Firm Name" and "CalcStat" like this:

    Firm Name        CalcStat

    abc LLC             0

    fedg LLC              1

     

    Then you could create a piechart with 3 values (and no categories);

    value 1 will have a count of the records with CalcStat=0;

    value 2 will have a count of the records with CalcStat=1;

    value 3 will have a count of the records with CalcStat=2;

    use Measured values in your formulas to count the number of Firms filtered by CalcStat...

  • Avatar
    Malinda Jepsen

    Thanks Adi!  That's exactly what I needed. I didn't realize that I didn't have to have Categories. 

  • Avatar
    Adi Hecht

    Glad this worked!

  • Avatar
    Malinda Jepsen

    Adi,

    After working with this more, I realize that I'm still missing the "minimum" part.  In your example it's "count of the records with MIN(CalcStat) = 0", etc.

    My formula currently looks like this: ([# of unique Firm Name],[CalcStatus]) ... CalcStatus is a filter

    But what I really want is to count # of  unique Firm Name where MIN(CalcStatus) = the filtered CalcStatus.

    Each Firm can have multiple CalcStatus at any given time and we want to count them at their "lowest" status. The SQL above probably over complicated what I'm looking for.  It's really a case of getting the list of firm names with their minimum CalcStatus and then counting them in the correct pie piece. It seems so simple, but I can't seem to find the right formula to accomplish it.

    Malinda

  • Avatar
    Adi Hecht

    So if I understand correctly, your table in your elasticube currently looks something like this:

    Firm Name        CalcStat

    firm A                      0

    firm B                      1

    firm A                       1

    firm B                       2

    firm A                        0

    firm C                       2

    and for the above example, the results would be:

    for the slice named calcstat0 --> 1

    for the slice named calcstat1 --> 1

    for the slice named calcstat2 --->1

    Am I correct?

    If this is the case then I think the best approach would be to create (another) custom table in your elasticube, in which you would create that "list of firm names with their minimum CalcStatus":

    something like:

    SELECT FirmName, min(Calcstat)
    FROM Your_Fact_Table
    GROUP by FirmName

     

    This custom table would contain the list of firm names, with their minimum calcstatus.

    Then in the dashboard, create the pie chart based on the new custom table you created.

  • Avatar
    Malinda Jepsen (Edited )

    Thanks again Adi! You are exactly correct. However, when I apply filters, the minimum value changes, so that's why I need the "dynamic" minimum. In your example, I have another column that they filter on.

    Firm Name        CalcStat    Format

    firm A                      0         Transaction

    firm B                      1         Transaction

    firm A                       1        Position

    firm B                       2        Position

    firm A                        0       Transaction

    firm C                       2        Position

    So when they filter on "Position" firmA will now be a 1 instead of the 0 and firmB will be 2 instead of 1. I really don't want to create every combination with the filters because we have over 75,000 rows each day and 5 filters.

    Malinda

  • Avatar
    Adi Hecht

    I see. I was not aware from your description that you had another dimension on which you needed to filter... this definitely complicates things.

    Basically the trick here would be to somehow calculate a measure which would give us the number unique firms which have both calcstat=0 AND calcstat=1 - if we had this measure we could deduct it from the count of unique firms which have calcstat=1 to get the number of unique firms which have calcstat=1 as their minimum value... 

    In a similar way we could count the number of firms which have calcstat=3 as their minimum value.

    So... I'm not this will work, but maybe:

     

    In your elasticube table, duplicate your calcstatus field to create an identical field named calcstat2.

    Then in your pie-chart dashboard:

    No categories.

    calculate value #1 as using a measured value with a filter like ([# of unique Firm Name],[CalcStatus])  and filter CalcStatus to a value of 0 only.

    calculate value #2 as ([# of unique Firm Name],[CalcStatus]) - ([# of unique Firm Name],[CalcStatus],[CalcStat2])

    but this time, filter Calcstatus to include 1 in the first part of the formula;

    And in the second part of the formula, filter CalcStatus to include only 0; and filter calcstat2 to include only 1;

    I didn't test this. But basically I am expecting the first part of the formula would give you the number of unique firms which have calcstatus=1; and the second part of the formula I believe will give you the number of firms which have both 0 AND 1. So by subtracting the second part from the first part, you should get the number of firms which have 1 but not 0, thus the number of firms for whom calcstat=1 is the minimum...

    And similarly you can calculate for value #3:

    ([# of unique Firm Name],[CalcStatus]) - ([# of unique Firm Name],[CalcStatus],[CalcStat2])

    but this time in the first part of the forlmula CalcStatus should be filtered to calcstatus=3.

    And in the second part of the formula: Calcstatus=3, calcstat2=(0 or 1).

     

    Maybe this would work?

Please sign in to leave a comment.