BookmarkSubscribeRSS Feed
Obsidian | Level 7

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



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.





Quartz | Level 8

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. 

Pyrite | Level 9

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,


Obsidian | Level 7

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.


Pyrite | Level 9

Yes, definitely.


  1. How to define My Week parameter
  2. Roles
  3. 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.

  4. 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,




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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
  • 4 replies
  • 3 in conversation