MicroStrategy: Creating a count metric of distinct occurrences of Attribute2 in Attribute1

Today I needed to create a simple count report metric without the need of creating a standalone metric in MicroStrategy.

What I needed was to count the number of appearences of certain attribute for the last 30 days. In other words, the metric of distinct occurences of “Days” in Attribute1.

You could easily achieve this in SQL using “WITH” clause. For instance (assuming table is at day level):

If you want to achieve this in MicroStrategy you need to create a report which includes attributes Date and Attribute1 for the last 30 days, and then you can create a report metric with following syntax:

Count Distinc MicroStrategy

Which means counting Distinct Date appearances at Attribute1 level without using lookup for attributes (if it used lookups it would ignore date metric when it’s not inside the GRID)

For further information about UseLookupForAttributes you can read:

  • UseLookupForAttributes =true, the aggregate from base/subtotal from the base metric calculates the attribute against the global lookup table.

The way to calculate function (Attribute) against the global lookup table is in cube join. First, generate a new template including all the attributes in the grid together with function (Attribute) metric. Calculate this new grid without a grid source (treat grid source as NULL), and then the exact same behavior could be obtained as the SQLEngine. The value is not be affected by the dataset which the metric is attached to.

  • UseLookupForAttributes = false, the metric will calculate against the source dataset (extended if necessary) which the metric is attached to. If the Attribute does not exist in the source dataset, cross join source dataset with Attribute and calculate function(Attribute) based on cross join result.

The value is affected by the dataset which the metric is attached to.

https://community.microstrategy.com/t5/tkb/articleprintpage/tkb-id/architect/article-id/6295

 

 

No comments yet.

Leave a Reply

MicroStrategy: Creating a count metric of distinct occurrences of Attribute2 in Attribute1

Today I needed to create a simple count report metric without the need of creating a standalone metric in MicroStrategy.

What I needed was to count the number of appearences of certain attribute for the last 30 days. In other words, the metric of distinct occurences of “Days” in Attribute1.

You could easily achieve this in SQL using “WITH” clause. For instance (assuming table is at day level):

If you want to achieve this in MicroStrategy you need to create a report which includes attributes Date and Attribute1 for the last 30 days, and then you can create a report metric with following syntax:

Count Distinc MicroStrategy

Which means counting Distinct Date appearances at Attribute1 level without using lookup for attributes (if it used lookups it would ignore date metric when it’s not inside the GRID)

For further information about UseLookupForAttributes you can read:

  • UseLookupForAttributes =true, the aggregate from base/subtotal from the base metric calculates the attribute against the global lookup table.

The way to calculate function (Attribute) against the global lookup table is in cube join. First, generate a new template including all the attributes in the grid together with function (Attribute) metric. Calculate this new grid without a grid source (treat grid source as NULL), and then the exact same behavior could be obtained as the SQLEngine. The value is not be affected by the dataset which the metric is attached to.

  • UseLookupForAttributes = false, the metric will calculate against the source dataset (extended if necessary) which the metric is attached to. If the Attribute does not exist in the source dataset, cross join source dataset with Attribute and calculate function(Attribute) based on cross join result.

The value is affected by the dataset which the metric is attached to.

https://community.microstrategy.com/t5/tkb/articleprintpage/tkb-id/architect/article-id/6295

 

 

No comments yet.

Leave a Reply

MicroStrategy: Creating a count metric of distinct occurrences of Attribute2 in Attribute1

Today I needed to create a simple count report metric without the need of creating a standalone metric in MicroStrategy.

What I needed was to count the number of appearences of certain attribute for the last 30 days. In other words, the metric of distinct occurences of “Days” in Attribute1.

You could easily achieve this in SQL using “WITH” clause. For instance (assuming table is at day level):

If you want to achieve this in MicroStrategy you need to create a report which includes attributes Date and Attribute1 for the last 30 days, and then you can create a report metric with following syntax:

Count Distinc MicroStrategy

Which means counting Distinct Date appearances at Attribute1 level without using lookup for attributes (if it used lookups it would ignore date metric when it’s not inside the GRID)

For further information about UseLookupForAttributes you can read:

  • UseLookupForAttributes =true, the aggregate from base/subtotal from the base metric calculates the attribute against the global lookup table.

The way to calculate function (Attribute) against the global lookup table is in cube join. First, generate a new template including all the attributes in the grid together with function (Attribute) metric. Calculate this new grid without a grid source (treat grid source as NULL), and then the exact same behavior could be obtained as the SQLEngine. The value is not be affected by the dataset which the metric is attached to.

  • UseLookupForAttributes = false, the metric will calculate against the source dataset (extended if necessary) which the metric is attached to. If the Attribute does not exist in the source dataset, cross join source dataset with Attribute and calculate function(Attribute) based on cross join result.

The value is affected by the dataset which the metric is attached to.

https://community.microstrategy.com/t5/tkb/articleprintpage/tkb-id/architect/article-id/6295

 

 

No comments yet.

Leave a Reply