7 Replies Latest reply on Jun 30, 2016 6:31 AM by Justus Niemzok

How to create bins of aggregated field?

Hello Guys,

I have two columns in my data 'Customer Name' and 'Transaction ID'. I have created two calculated fields one is Cust_Count = CountD([Customer Name]) and another one is Trans_Count = CountD([Transaction ID]). Based on these calculated fields I have created a simple view stating Customer Name and their Transaction Count (Kindly refer attached twbx file). Now I want to create another view to identify how many customers (Customer Count) has 22 transaction Count and how many has 21 transaction count.

Trans Count is an aggregated hence its not giving an option to create bins. So any one can help me out in this?

Regards,

Harshal Bhavsar

+91 9867994238

• 1. Re: How to create bins of aggregated field?

Hi Harshal,

I think the easiest would be to create the bins via a calculated field like in this guide.

Basically the calculation looks like:

IF [Trans count] = 21

THEN "21"

ELSEIF [Trans count] = 22

THEN "22"

END

Through a calculation similar to this you can create any custom bins.

Alternatively if you really only want a bin size of 1, you could drag your [Trans Count] to the dimensions pane (or duplicate and then frag into dimensions). That way you can bring it into the view to give you headers.

Kind regards,

Justus

InterWorks

• 2. Re: How to create bins of aggregated field?

Is that what you wanted?

workbook has been attached for your reference. Let me know If you've any query.

Mahfooj

• 3. Re: How to create bins of aggregated field?

Thank you Justus and Mahooj for your quick replies. These seems fine but I have to apply it on my complete database and there are around lakhs of unique customers and their transaction count can vary up to 1000. Its very difficult to write formula for each bin size of transaction. Is there any alternative solution for this?

Justus Niemzok: Transaction Count being an aggregated field it cannot be converted in to dimension. I am trying to pull it in dimension table but it still remains in measure.

• 4. Re: How to create bins of aggregated field?

Thank you very much Mahooj...

• 5. Re: How to create bins of aggregated field?

Good point Harshal, sorry I missed that. Of course you cannot drag an aggregated measure into the dimensions.

You can though create bins based on LoD calculations. So I guess what you need is a count of transactions per customer. Then create a bin on that. See picture and attached workbook.

Let me know if this helps you.

Kind regards,

Justus

InterWorks

1 of 1 people found this helpful
• 6. Re: How to create bins of aggregated field?

Awesome.. That's solve my problem.. Thank you very much Justus Niemzok.......