Calcite | Level 5

## SASVA count distinct of certain column

I have the following data, I'd like to create a report with a cross table.

 rec_id company_id signals month 1000 1 1 October 1001 1 0 October 1002 2 1 October 1003 2 1 October 1004 2 0 October 1005 3 1 October

In the report I would like to have

the count of the rec_id

the distinct count of company_id (which I did with aggregated value and distinct)

the sum of signals

a signal per company_id so for company A 1 signal, B 1 signal (not 2 signals) and C 1 signal

The signal column doesn't have values more than 1.

How can I create such an aggregated value?

2 REPLIES 2
Pyrite | Level 9

## Re: SASVA count distinct of certain column

Hello @GreyHamster ,

1. Which version of VA you have?
2. You mentioned that you'd like to create a report with a cross table. How should the resulting cross table look like? Could you sketch it?

Creating needed variables:

• the count of the rec_id
Hover mouse over rec_id > click r-m-b > New calculation... > Type = Count
• the distinct count of company_id (which I did with aggregated value and distinct)
Hover mouse over company_id > click r-m-b > New calculation... > Type = Distinct Count
• the sum of signals
Check that measures aggregation is Sum
• a signal per company_id so for company A 1 signal, B 1 signal (not 2 signals) and C 1 signal
I'm not sure if I understand this. Will the result be always 1? Is there any possibility that it will be zero or something else? If it is always one then let's just create a New Calculated Item with simple 1 and set aggregation to Min or Max. If it's something else we might need utilize AggregateTableI() operator. And finally how should this result be embedded to the Cross table

Best regards,

Petri

Calcite | Level 5

## Re: SASVA count distinct of certain column

Thanks for your answers, with AggregateTable it can probably do what I would want to do, unfortunately I'm on version 7.5.

I want to know whether there is a signal for a company.
If I would build a query I would create an aggregated table with company_id and the sum of signals and another query to check if the signal=>1 then 1 else 0 for each company.

Instead of creating a new datasource I would want to do it in an expression. If I can't use aggregatetable, is there another way?
Discussion stats
• 2 replies
• 3072 views
• 0 likes
• 2 in conversation