BookmarkSubscribeRSS Feed
GreyHamster
Calcite | Level 5

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

rec_idcompany_idsignalsmonth
100011October
100110October
100221October
100321October
100420October
100531October

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
PetriRoine
Pyrite | Level 9

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

 

GreyHamster
Calcite | Level 5
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?

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 3072 views
  • 0 likes
  • 2 in conversation