I have a histogram using COUNT to aggregate. ...

[Mike_A]

I have a histogram using COUNT to aggregate.

I am trying to figure out a way to only Count UNIQUE VALUES and plot that in the chart.

Any suggestions how to do this in appsheet (rather than in the original gsheet)?

[Steven_Coile]

You need to find a way to construct a table or slice that includes only the unique values. Constructing a table is the more-efficient method, but requires manual management as values come and go. A slice would be dynamic, but resource-intensive. You could then plot from the table or slice.

[Mike_A]

Thanks +Steve Coile!

Sounds like the “smart” approach is to create an intermediary table in the gsheet, to avoid performance hits.

Only reason not to do this is I was trying to avoid creating another “tab” in the gsheet as its a shared sheet.

I will try this and see how it works.

If i was to try the “select” approach in a slice, I am not clear on how to get that slice set up.

Do I set up a virtual column, or do I always need a table in the gsheet to do this?

select(Products[Price], true, true) … where do I add this as an appformula?

Table/Slice?

[Steven_Coile]

@Mike_Adler I’d think you’d need 1 row per data point. Each row would identify one of the distinct values for which you want a count, and would have a virtual column with an appropriate count(select(…, true)) expression.

Suppose we want to graph the number of distinct prices per product SKU.

The known product SKUs are comprised of the values in the [SKU] column of the Products table.

Let’s call the table of graph-able data Graph.

We need to tie each graph point to a product SKU, so we need a column in Graph, let’s call it [ProductSKU], that contains the distinct SKU for which distinct prices are to be counted.

The virtual column in the Graph table with the value to be graphed might be called [SKUPricesCount], with an App formula of =count(select(Products[Price], in([SKU], list([_thisrow].[ProductSKU])), true)).

[Mike_A]

+Steve Coile - thanks.

will give it a shot…

[Steven_Coile]

The select() function can produce a list of distinct (non-duplicate) values if you make the third argument true.

The following produces a list of all values from the [Price] column of the Products table, including duplicate values:

select(Products[Price], true)

The above is equivalent to:

select(Products[Price], true, false)

That third argument, if omitted, defaults to false. If true, the results will include only distinct values.

The following produces a list of only the distinct values:

select(Products[Price], true, true)

Then you can count the results:

count(select(Products[Price], true, true))

[Austin_Kerr]

I had a similar issue where I wanted to see it week month and year aggregates, I did this by adding virtual columns that had the different category’s for date set using time intelligence, then was able select those day month year columns for the sum aggregate function in a histogram. I’m posting this reply for posterity