Distinct Dimension count with metric => x

[mubarakd5726232]

I've noted the function 'Approximate Count Distinct' which could be used for a count of the distinct values for a dimension however I'd like to extend this further and only count the distinct count where the value has had => specified metric value.

eg.

Distinct Headline count that have had 10 or more Page Views.

I'm able to achieve this if the table breakdown (rows) is headline but not without.  

[PratheepArunRaj]

Dear Mubarak,

Thanks for your idea to count the unique items of a variable using Calculated Metric. It was a new technique to me!

For the above, adding Segment to the function will help to solve?

What i have done is,

Created a segment with Page Views >= 10 in a Visit and used that in Calculated Metric.

First Segment Container and then Function for distinct count.

Will this help?

Thank You!

Arun

[mubarakd5726232]

Arun,

Thanks for the suggestion, let me clarify further..

I'd like to report on articles that have been viewed more than x no. of times in a specified time frame and not by visitor/visit container.

so I could then report on ..

1. No of articles published (distinct count)

2. No of articles published with 10 or more page views

.. to then produce KPI % of articles published with more/less than 10 page views.

as your segment is visit based it wouldn't do what's required.

[Gigazelle]

Viewed X number of times per individual, or total across your site?

[mubarakd5726232]

Across site/network

Take the following as an example ..

We've deemed individual articles being viewed 100+ times as being a 'quality' content measurement, So the above highlights 76% of are articles met this requirement/KPI and generating the significant proportion of the overall page views.

The above has been produced using row count and a calculated metric which is reliant on row count.

The above is a workaround and does not do/allow ..

1. Have this trended (each month would require separate table)

2. Have segment applied to 'headlines' dimension and it only show corresponding articles eg. articles that were viewed less than 100 times individually)

ps. this concept could apply to other market places too eg. retail show unpopular products that have been viewed/purchased less than x times

[Gigazelle]

I know that conditional (if/then) calculated metrics can accomplish this. Basically say that if a given line item has less than X, drop it to 0 so it's excluded from the report entirely. Then count the number of distinct line items and use that in your report.

[mubarakd5726232]

That's what I've done to achieve dashboard above.. but this is limited and does not allow for what I mentioned in the previous post

[Gigazelle]

Can you provide more info on why this data would not be able to be trended? I'm also unsure what you mean by headlines and it only show corresponding articles. If you could give more details on your observed limitations, we can explore further whether it is available in Workspace or not.

[mubarakd5726232]

eg ...

The highlighted metrics was produced relying on row count with calculated metric on a table for specified time frame. ie to produce this metric for different timeframes (for trended) would require separate tables ... and you can only trend data in a single table rather than across multiple tables.

So in order to produce a trended visualization  I need to be able to create the required metric which can work on date dimension in a single table

[marion33678522]

You can still apply diferent time ranges on top of your metric if this one is on the rows. I usually do this and works pretty well. I have a time range for each month.

So what I do is putting many times the same metric, and addind diferent time ranges on top. Then you can trend each ROW in the same plot.

I'm interested on your calculated metric ! could you share ?!

Kind regards,

[mubarakd5726232]

Marion ..

One of the calculated metrics below, this sums up the page views for articles that had 100+ PVs in the specified time frame.  There is a similar which does a distinct count of the rows too

With regards being able to trend I'm not sure I know what you mean.  As you'll note from the calculated metric it sums up the rows (or count of distinct rows in the other metric) .. therefore I have to have a separate table for each month/duration and thus can't trend.

ps. I'm able to trend (bar chart) a basic metric using the APPROXIMATE COUNT DISTINCT function, however the above metrics are doing a little more than just counting distinct values .. ie distinct value where pvs for value > 100+

[marion33678522]

I was talking about putting a Time Range on top of your metric, but yeah, I'm not sure it will work with the column sum, since it will probably count the columns of the current time :

[Gigazelle · Accepted solution]

I don't think there's a solid answer here within workspace, as there is a paradox of requirements:

• The ability to trend articles such as this would necessitate a segment, as separating it by month would require the 100+ page view requirement to be met every month, not just across the reporting period.
• Segments are based on users to your site (hits/visits/visitors), not variable values. A segment cannot be defined as "only use articles where there were more than 100 page views across the reporting range". That definition couldn't fit into a hit/visit/visitor container. This is why a calculated metric works.

The only way I anticipate you'd be able to do this is by using report builder to create a separate request for each date range, then create a line chart in excel that takes the table values and trends them.

[JimKultgen]

Pretty certain you can achieve this if you are willing to use a bar graph in lieu of a line graph.

Calculated Metric

Column Sum [If page views > 100, 1, else 0]

Use month as a dynamic column header below the metric.

Drop page or whatever you dimension is into the table.

Select the dimension title and viz as a bar graph.

This works for values that exceed a threshold, its impossible to use this for values below a threshold (less than 100) because if a user doesn't view a product at least once it would not exist in the reporting window (IE would only return values that were seen at least once in the overall time frame, but less than 100 times.  If that is satisfactory you could just flip the 0's and 1's in the calculated metric.