1 Reply Latest reply: Jul 14, 2017 12:13 PM by Sunny Talwar RSS

    how to get this complicated requirement

    naveen kumar

      Hello All, My requirement  is to differentiate multi ids and single ids materials when respective fiscal year or month or quarter selected 

       

      Fiscal_Year  Fiscal_Quarter Fiscal_Month   Material    Id       Amount

                  2017             Q1             Apr          C001      D01       10

                  2017             Q1             Apr          A001      D01       10

                  2017             Q1             Apr          A001      D02       50  

                  2017             Q1             Apr          B001      D01       20

                  2017             Q2             Aug          A001      H05       90

                  2017             Q3             Oct          B001      H05       60

                  2017             Q4             Jan          L001      N06       50

       

      and when i select only Fiscal year = 2017

      Table-1

      Dimension:Material,Id

      Expression For Table-1 is used:  Sum({<Material={"=Count(Distinct Id)>1"}>}Amount)

      Table-2

      Dimension :Material,Id

      Expression For Table-2 is used:  Sum({<Material={"=Count(Distinct Id)=1"}>}Amount)

      I am getting below table  which is  perfect

         

           Multi ids table                               Single ids table

           ---------------                               -----------------

      Material    Id     Amount                     Material    Id      Amount

      A001      D01       10                         C001      D01       10

      A001      D02       50                         L001      N06       50

      A001      H05       90

      B001      D01       20

      B001      H05       60

      and when i select Fiscal year 2017  and either month Apr or Quarter Q1, i am get below tables, in which material B001 has moved  to single ids table ,

      Actually  which was in multi ids table when only  Fiscal year 2017 was selected 


         Multi ids table                               Single ids table

          ---------------                                ---------------

      Material    Id     Amount                     Material    Id     Amount

      A001      D01       10                        C001      D01       10

      A001      D02       50                        B001      D01       20

      but what i need is,i want Material B001 in multi ids table even though Apr or Q1 has selected

      because when only 2017 is  selected this material is having  more then one ids.

      Required below table when Apr or Q1 has selected

             Multi ids table                               Single ids table

           --------------                                 --------------

      Material    Id     Spend                     Material    Id     Spend

      A001      D01       10                        C001      D01       10

      A001      D02       50

      B001      D01       20

      can any one suggest me what changes do i need to do in my expression to achieve above requirement

       

       

      thanks