3 Replies Latest reply: Jul 15, 2016 11:32 PM by Sunny Talwar RSS

    Issues with missing values

    Genadij Bojev

      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

        • Re: Issues with missing values
          Sunny Talwar

          Check for the second issue now

           

          Capture.PNG

            • Re: Issues with missing values
              Genadij Bojev

              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

                • Re: Issues with missing values
                  Sunny Talwar

                  Not the best of the solutions, but it works:

                   

                  Capture.PNG

                   

                  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))