Beastmode field not aggregating in bar chart

Options

I have a dataset containing the following data:

The Sessions field is a beastmode calculating the rank of the customers phone number. (Running total of how many transactions they have)

rank() over (partition by Location.ID, PhoneNo order by TransactionDate)

I am wanting to create a horizontal bar chart with the number of sessions on the Y axis and the count of customers with that many sessions along the x axis like this (based on the data above):

But when I put the data in a horizontal bar chart, the Sessions field is not aggregating and resulting in this:

How can I get the Sessions field to aggregate in the bar chart?

I am wanting Sessions to be calculated as a beastmode so the user can select a date range and the Sessions will update based on that date range so I can't calculate the sessions in an ETL.

Best Answer

  • DavidChurchman
    Answer ✓
    Options

    You could do this with a Histogram (though the labels would be slightly different than your picture). "Category Names" would be the phone numbers and "Column containing data set" could be a beastmode like COUNT(TransactionDate) or COUNT(DISTINCT TransactionDate)

    To keep it as a horizontal bar chart, I would use two beastmodes:

    • Y axis: COUNT(DISTINCT TransactionDate)
    • X axis: COUNT(DISTINCT MaskedPhoneNo)
    • Tooltip: MaskedPhoneNo

    Make sure the Tooltip is not aggregated. That's what is going to let your card know that you don't want to pool all of the customers into one bucket. You could also put the MaskedPhoneNo as the series, which would allow you to see which customers fall into which bucket, maybe with some hover text. Only problem is it gets pretty busy pretty quickly, depending how many customers/bar.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • Sean_Tully
    Sean_Tully Contributor
    Options

    Do you have any sorting going on that might be making it deaggregate?

  • nickcorona
    Options

    @Sean_Tully No the unaggregated data appears when I have sorting/filtering applied and when there are none.

  • Sean_Tully
    Sean_Tully Contributor
    Options

    Sorry, was playing around with it and couldn't make it work. I suspect the analyzer doesn't like the rank function for this purpose, but I'm not sure. Interestingly, if I put the phone number on the card as a series, the bars did stack and it looked more correct, but I'm sure you don't want to see them stacked like that. I'd suggest you mark this unanswered so someone else can pick it up and hopefully solve it for you.

  • DavidChurchman
    Answer ✓
    Options

    You could do this with a Histogram (though the labels would be slightly different than your picture). "Category Names" would be the phone numbers and "Column containing data set" could be a beastmode like COUNT(TransactionDate) or COUNT(DISTINCT TransactionDate)

    To keep it as a horizontal bar chart, I would use two beastmodes:

    • Y axis: COUNT(DISTINCT TransactionDate)
    • X axis: COUNT(DISTINCT MaskedPhoneNo)
    • Tooltip: MaskedPhoneNo

    Make sure the Tooltip is not aggregated. That's what is going to let your card know that you don't want to pool all of the customers into one bucket. You could also put the MaskedPhoneNo as the series, which would allow you to see which customers fall into which bucket, maybe with some hover text. Only problem is it gets pretty busy pretty quickly, depending how many customers/bar.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.