Obsidian | Level 7

## Cross table issue

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

4 REPLIES 4
Quartz | Level 8

## Re: Cross table issue

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

## Re: Cross table issue

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

Obsidian | Level 7

## Re: Cross table issue

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

## Re: Cross table issue

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

Petri

Discussion stats
• 4 replies
• 569 views
• 2 likes
• 3 in conversation