SAS visual analytics how to reuse an aggregated measure

[gabras]

Hi is it possibile to reuse a calcolare data item in further visualization?
I have
CustId. ID
1. 1
1. 2
2. 3
3. 4

And i want

CustId Count
1. 2
2. 1
3. 1

And i watt to reuse Count in order to count how many Cust have tua distinct value of count. So:

Count CountDistCustid
1. 2
2. 1

How can i do it using VA 7.3?
I thougth also about creating a sequence variable but it seems not possible in VA.

Thank you

[SASKiwi]

This can be easily done in SQL outside of VA as two separate tables, then loaded into VA.

What type of reports do you want to do? Some reports will do counts without the need to prepare your data.

[gabras]

Hi @SASKiwi,

You are rigth, it is Simone outside VA. But if you build it outsider va,the report in the VA will be static. You even can’t change the the period of time otherwise there is a risk that the column calculated out of the VA is completely wrong

[gabras]

@SASKiwi i would like to have a report that counts how many CustId had that amount of Interaction with the company. But i would like to see the same thing in a different period of time.

[SASKiwi]

@gabras - If you want a solution that has a time dimension you need to add that to the data you posted so I know what your complete requirements are. As long as you summarise to the lowest level of granularity in your time dimension then that will work fine in VA. I have done this myself.

[gabras]

Hi @SASKiwi

let's say i have 

ID	CUSTID	DATE
1	1	01/01/2018
2	1	01/02/2018
3	1	02/02/2018
4	2	02/01/2018
5	2	03/01/2018
6	3	04/02/2018
7	3	05/02/2018

and i want  to count how may interaction per custid

CUSTID	Count
1	3
2	2
3	2

and then how may distinct CF have that amount of count

Count	CountDistCustId
2	2
3	1

Now, i want to filter the data from february 2018 and so the different tables should become:

ID	CUSTID	DATE
1	1	01/02/2018
2	1	02/02/2018
3	3	04/02/2018
4	3	05/02/2018

CUSTID	Count
1	2
3	2

Count	CountDistCustId
2	2

i hope it is clear

Thank you

[SASKiwi]

If I understand your customer count correctly, something like this should work. When you then apply a filter on date VA will only include the customer counts that obey the filter.

proc sql;
  create table want as 
  select date
        ,custid
        ,count(*) as Count
  from have
  group by date
          ,custid
  ;
quit;