Cross table issue

[gyambqt]

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

[ASHISH2525]

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. 

[PetriRoine]

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

[gyambqt]

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.

[PetriRoine]

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,

Petri