Pivot table using ValueList as Rows

[BoXiangWang]

Hi QS expert,

I am using the pivot table to show the value.

I have two layers of rows:

The first one is the following expression, with the label "Group"

=ValueList('Overall','Indirect HKG','Indirect Region','Cabin Crew','Cockpit Crew','Direct (Others)')

The second one is, with label "YearMonth"

=
Aggr(only({<[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>}
[date_month.autoCalendar.YearMonth]),[date_month.autoCalendar.YearMonth])

For the Value I put the following, labeled as "Turnover"

=

Pick(

Match(ValueList('Overall','Indirect HKG','Indirect Region','Cabin Crew','Cockpit Crew','Direct (Others)'),

'Overall','Indirect HKG','Indirect Region','Cabin Crew','Cockpit Crew','Direct (Others)'),

sum(aggr(count(distinct {<action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month)),

sum(aggr(count(distinct {<direct_indirect_ind={"I"}, Region={'HKG'}, action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month)),

sum(aggr(count(distinct {<direct_indirect_ind={"I"}, Region={'Region'}, action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month)),

sum(aggr(count(distinct {<employee_class={'CA'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month)),

sum(aggr(count(distinct {<employee_class={'CO'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month)),

sum(aggr(count(distinct {<direct_indirect_ind={'D'}, employee_class-={'CA', 'CO'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month))

)

The table shows:

I've checked the expressions but didn't find error. I wonder why only the first row shows the value?

[Kushal_Chawda]

@BoXiangWang  try with aggr including YearMonth dimension

Pick(

Match(ValueList('Overall','Indirect HKG','Indirect Region','Cabin Crew','Cockpit Crew','Direct (Others)'),

'Overall','Indirect HKG','Indirect Region','Cabin Crew','Cockpit Crew','Direct (Others)'),

sum(aggr(count(distinct {<action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month,[date_month.autoCalendar.YearMonth])),

sum(aggr(count(distinct {<direct_indirect_ind={"I"}, Region={'HKG'}, action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month,[date_month.autoCalendar.YearMonth])),

sum(aggr(count(distinct {<direct_indirect_ind={"I"}, Region={'Region'}, action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month,[date_month.autoCalendar.YearMonth])),

sum(aggr(count(distinct {<employee_class={'CA'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month,[date_month.autoCalendar.YearMonth])),

sum(aggr(count(distinct {<employee_class={'CO'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month,[date_month.autoCalendar.YearMonth])),

sum(aggr(count(distinct {<direct_indirect_ind={'D'}, employee_class-={'CA', 'CO'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]), date_month,[date_month.autoCalendar.YearMonth]))

)

[BoXiangWang]

Hi Kushal,

Thanks for your reply. I did it but still the table showed 0 apart from the first row.

[BoXiangWang]

I tried to debug it and tested the following expression, which gave me the outcomes without aggregating and then summing. The result is not what I want. I don't get it why the first row(Overall) could fetch the data but others couldn't.

=

Pick(

Match(

ValueList('Overall','Indirect HKG','Indirect Region','Cabin Crew','Cockpit Crew','Direct (Others)'),

        'Overall','Indirect HKG','Indirect Region','Cabin Crew','Cockpit Crew','Direct (Others)'),

count(distinct {<action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]),

count(distinct {<direct_indirect_ind={"I"}, Region={'HKG'}, action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]),

count(distinct {<direct_indirect_ind={"I"}, Region={'Region'}, action_group={'Termination'}, 

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]),

count(distinct {<employee_class={'CA'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]),

count(distinct {<employee_class={'CO'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk]),

count(distinct {<direct_indirect_ind={'D'}, employee_class-={'CA', 'CO'}, action_group={'Termination'},

[date_month]={">=$(=AddMonths(vRollingDate,-11))<=$(=MonthEnd(vRollingDate))"}>} [dim_ppl_employee_sk])

)