How to create a chart with buckets? (small guide)

 

Small guide on how to create buckets in Targit using "user dimension" and "calculations".
This is the result we are aiming for. Show how many customers view a tarigt report "<= 2 times", ">2 and <=5 time", ">5 and <=10 times", "> 10 times".

 

If we take a closer look at the components used we can see the "user dimension" which will hold our custom buckets, the customers which we want to divide in buckets accordingly the number of times they viewed a Targit report.

First we start by adding a "user dimension". We wanted to have 4 buckets so we created 4 members in the user dimension without selecting any criteria.

In the next step we add as many calculations "type measure" as there are buckets in the user dimension.
We give them a proper name so we have an idea what they will be displaying

For each calculation you check if the "number of views" measure is lesser, bigger or between your bucket ranges as follows:

Now we have a separate measures and for the chart we of course need one measure to count the number of customers in each bucket. That is why we need to add a column measure as follows

The bucket id is a helper measure to count the rows, the "bucket total" will show the correct measure result depending on the "user dimension" member.
So using the "bucket id" measure we can now identify a row number per member of the user dimension.
The total measure will check on which row we are and will do a sum of all relevant bucket measures.
So on row 1 we want to have a sum of all the " bucket views <= 2" measures.
On row 2 we want to have a sum or all the "bucket views >2 and <=5" measures.
And so on.

If we did all this you will have something like this in the crosstab:
I masked the customer names in the image below with a fixed label. So in the actual data there are different customers of course

As you can see we still need to hide some stuff before switching to the chart presentation.
First hide the "bucket id" calculation which is just there for our information

Also we need to get rid of the customers. We can achieve this by hiding a range of this dimension. Define your range starting from the first element and ending on the last element.

And now we are ready to switch to a column chart and have the result we wanted to achieve.
You might have to add a dynamic label for each member in the user dimension to show the correct bucket label.
So "bucket1" will have a dynamic label "<=2 views", "bucket 2" will have a dynamic label ">2 and <=5 views", etc...


4

Comments

9 comments
  • Really nice solution!

    0
  • Nice!
    Can you also explain how to create buckets based on calculated days?
    For example @Today - OrderDate. And based on that calculation create buckets: 0-5 days, 6-10 days, 11-20 days, >20 days.

    Dankjewel!

    0
  • Hi Jora

    Since Dynamic dates are always calculated from today, you should be able to make a user dimension with different dynamic filters to create these buckets.

    First create a user dimension with the title "Order date buckets" and add your first element:

    Now choose your order date hierarchy and go to dynamic date editor - set the operator to between - set the result level to day set the offset to today - 5:

    Then set the "To:" - also result level = days - no offset

    When you click ok - you have your first bucket with orders between 0 and 5 days old - then you click add element and repeat the proces for the next buckets.

    Last bucket will be a little different - since we set the operator to greater than - result level to days and offset to -20:

     

    Now we have a nice user dimension with order date buckets ready to use:

     

    br/Niels

    1
  • Thank you Niels!

    0
  • Ok, I tried but my buckets don't show the periods like yours do.

    When selecting the order date hierarchy I see
    Please advise.

    0
  • Hi Jora

    It looks like there is a problem with the way your dynamic dates are set up. I doesn't seem to work on the date level. It's probably a matter of a few definitions being fixed - did you set it up yourself or did someone help you?

    br/Niels

    0
  • Hello Niels,
    I figured there was already a hierarchy set up and I used that one.
    I tried the same using the different Posting date hierarchies but they act the same.
    Maybe I should create a new one. I found an article that might be useful for this
    https://community.targit.com/hc/en-us/articles/360017099097-Dynamic-Time
    Should that be the one?
    Jora

    0
  • Ok - if you're making a relational model on top of a relational database or maybe TARGITs inMemory database - that's the right article.

    If you have a cube (tabular or OLAP) maybe this article is more helpful: https://community.targit.com/hc/en-us/articles/360017188717-Enabling-Dynamic-Criteria-through-Decorations%C2%A0 

    br/Niels

     

     

    0
  • Thank you Niels,

    I fixed the date and created a relation with the table since this was missing.

    The buckets are working fine now!

    Jora

    0

Please sign in to leave a comment.

Didn't find what you were looking for?

New post