Issues with missing values

[#1]

Hi,

I have trouble including dimension values into pivot table. Please see attached example.

In Example 1 pivot table, I would like to see both genders for each job. Is it possible to achieve and how with given data model? If the model should be changed - how?

Another issue is that I'm trying to use alt states to exclude people from lists or calculations (same attachment). When I select someone to be excluded in Exclude Emplid listbox, this person is removed from Example 2 table. But there is a problem, that Grade and Points, that this person had, are also removed from other rows. How can avoid that?

I've tried searching for possible solutions with no luck, so any help or guidance is much appreciated

[sunny_talwar]

Check for the second issue now

[#3]

Thank you @Sunny T, your solution worked. Although when I tried to exclude selected employees from the calculations in pivot table, I've stumbled upon same issues. Luckily I've found another way of excluding values selected in alternate state by using following code:

If(Len(Trim(GetFieldSelections(Emplid, ',',1, 'AS1'))) > 0,

Sum({$<Emplid=e({AS1}Emplid)>}Salary),

Sum(Salary)

)

However the first issue still remains

[sunny_talwar · Accepted solution]

Not the best of the solutions, but it works:

Script:

Employee:

LOAD * INLINE [

Emplid, Name, Gender

111, John, M

113, Maria, F

123, Peter, M

124, Petra, F

321, Jason, M

126, Steve, M

221, Benjamin, M

432, Mark, M

998, Mikael, M

356, Sandra, F

];

Transactions:

LOAD * INLINE [

Emplid, Salary, Job

111, 500, aaa

113, 500, aaa

123, 600, bbb

124, 700, bbb

321, 800, ccc

126, 400, ddd

221, 300, bbb

432, 230, aaa

998, 400, ddd

356, 200, eee

];

Jobs:

LOAD * INLINE [

Job, Grade, Points

aaa, 12, 245

bbb, 13, 289

ccc, 14, 321

ddd, 14, 332

eee, 14, 332

];

Left Join (Jobs)

LOAD * Inline [

Gender1

M

F

];

Pivot Table:

Dimensions:

Gender

Points

Job

Gender1

Expression:

Sum(If(Gender = Gender1, Salary))