Count Calculation Error

[glam]

Hi All - I’m trying to create a calculated field in which it is based off of 3 other calculated fields.

a) Unique ID Count: distinct_count of an ID
b) Initial Cost: the cost for the first 2 initial counts
c) Additional Cost: the cost for an additional count after the initial cost

I have tried creating several new calculated fields to do this calculation; however have been getting different errors (nesting not allowed, aggregated vs non aggregated not allowed…) Is there anyway around this?

ifelse({Unique ID Count}<=2,{Initial Cost},max(({Unique ID Count}-2))*{Additional Cost})

Appreciate the help in advance!

[andrepgn]

Hi @glam,
the problem here is that you are using further aggregation on some (calculated) fields and not on others.
Can you please provide a sample of (anonymized) data and the calculation you have created for the first three fields?

A workaround for a calculated field created from other calculated fields is using an aggregation formula also when is not strictly necessary, but let us have more data and we can try to guide you better!

Thank you,
Andrea

[glam]

Hi @andrepgn ,

See sample dataset below

a) Unique ID formula: does a distinct count of the Unique ID (i.e. ABC will show a count of 1 as there is only 1 unique ID for ABC ID, where as BCD will show 3 as there are 3 unique IDs for BCD ID…)

b) Based off the Type, the charge will be different. If the count is less than or equal to 2, in total (regardless if count is 1 or 2), x will have cost $1, Y will cost $2, Z will cost $3, and W will cost $4. If the cost is more than 2, the first 2 will still following the “less than or equal to 2” logic. Any additional count will cost half the amount (i.e. if the count for BCD is 3, then that will cost $2 (for the first 2) and $1 for the additional totaling $3.

Essentially I will like a table to calculate the total cost based off the ID (something similar to the below)

Many Thanks in advance.

[andrepgn]

Hi @glam ,
supposing that the DEF should have a W as type (otherwise I don’t know how to obtain that 4 as final cost ), here is a way to obtain what you want:

1. First, for convenience, let’s calculate the unit prices in a calculated field

Unit_Full_Price = 
    switch(Type,
        "X", 1,
        "Y", 2,
        "Z", 3,
        "W", 4,
        0
    )

2. Now let’s calculate the Distinct Count for the UniqueID field:

UniqueId_Distinct = distinctCountOver(UniqueID,[ID],PRE_FILTER)

3. Let’s move to calculate the price for the 1 or 2 units sold:

First_2_Price = 
    ifelse(
        // For counts of 1 or 2, apply base rate
        {UniqueID_Distinct} > 0, {Unit_Full_Price} ,
        // If 0
        0
    )

4. Let’s now move to the Additional price:

Additional = 
    ifelse(
        {UniqueID_Distinct} > 2, ( {UniqueID_Distinct} - 2 ) * {Unit_Full_Price} / 2,
        0    
    )

5. And finally let’s get the total price:

Final Cost = {First_2_Price} + Additional

6. To obtain the final result, just add the fields to your table. Please note that I’ve moved all the fields selected in the Group By section. If you move some of the values in the Value section, just remember to change the aggregation from sum to anything btw avg, min or max, otherwise you will see the wrong values

Hope that this helps!

Andrea

[glam]

Thanks Andrew - above works!

[andrepgn]

Thank you @glam , see you soon!
I’ll close the topic