topic Re: Cross table issue in SAS Visual Analytics

Cross table issue

gyambqt — Tue, 07 May 2019 06:56:37 GMT

Hi Experts,

Assume I have the following data and want to create cross table in VA.

Week number(date). Age group. Number of people

1 1-30 1

1 31-60 2

1 61 and above 3

2 31-60 1

2 61 and above 2

My new calculated item will be relative function in period with -1 on week number for number of people

so you have

Week number(date). Age group. Number of people. Week-1(calculated)

1 1-30 1 .

1 31-60 2 .

1 61 and above 3 .

2 31-60 1 2( from week1)

2 61 and above 2 3 (from week1)

now i I want to create a cross table in VA 8.3 with filter week=2

the row variable is age group and column variable is week-1 I would get:

Filter is week=2

Week-1

age 31-60 2

age 61 and above. 3

age 1-30 is missing from this cross table as this category does not exist in week2. But I want to see it here as the column I used is week-1. How do I get this missing category displayed in the cross table without manipulating the source data.

thanks

Re: Cross table issue

ASHISH2525 — Tue, 07 May 2019 10:16:13 GMT

I did not actually get why do you want to have 1-30 age group in filtered data when you already applied filter = week 2.

However if you want to achieve the desired you will have to use multi-conditional filter eg: OR , AND. you can use Boolean operator for the same.

Re: Cross table issue

PetriRoine — Tue, 07 May 2019 13:11:08 GMT

Hello @gyambqt,

I agree with using multi-conditional rules. I did a little experimenting.

Drop down list populates My Week parameter.

IF ( 'My Week'p NotMissing )
RETURN ( ( TreatAs(_Number_, 'My Week'p) = ( 'Week Num'n + 1 ) )
OR ( TreatAs(_Number_, 'My Week'p) = TreatAs(_Number_,
'Week Num'n) ) )
ELSE ( 1 = 1 )

This seemed to work as you described.

Best regards,

Petri

Re: Cross table issue

gyambqt — Wed, 08 May 2019 00:34:24 GMT

Thanks for your prompt reply.
Could you show me your screenshots for other processes?

1. How you define l my week parameter

2. role of cross table

3. detailed of week filter?

5.How you have created people (week-1)? I used relative function with -1 in week

the week here is a date variable and it seems in your testing data you have used a numeric value for that.

Re: Cross table issue

PetriRoine — Wed, 08 May 2019 09:40:38 GMT

Yes, definitely.

How to define My Week parameter
Roles
Details of the filter
Go to Filters in the Right pane.
Click + New filter.
Click Advanced filter.
There are two modes to create/edit filters: Visual and Text. Select Text mode from the top of the window.
Copy paste this to the screen:
```
IF ( 'My Week'p NotMissing )
RETURN ( ( TreatAs(_Number_, 'My Week'p) = ( 'Week Num'n + 1 ) )
OR ( TreatAs(_Number_, 'My Week'p) = TreatAs(_Number_,
'Week Num'n) ) )
ELSE ( 1 = 1 )
```
The OK button is disabled (grey). Click somewhere on the window's canvas to make the OK button activate (blue) and click OK.
I didn't create "Week-1" in the VA - I had it in the data directly. The data doesn't have a proper date variable so one can't utilize f.ex. VA's period operators. Also VA doesn't have LAG function which would enable returning values from rows before.

I hope this helps 😃

Best regards,

Petri