7 Replies Latest reply: Jul 27, 2017 6:54 AM by Tresesco B RSS

    how to get distinct count

    naveen kumar

      Hi

      Below i have sample table

      Material  Plant    Vendor          Fromdate       Price

      001        XXX       AAA          27-6-2017        100

      001        XXX       AAA           01-06-2017       200

      001        XXX       BBB           22-07-2017        20

      001        YYY       CCC           23-07-2017        10

      001        YYY       CCC           01-07-2017        30

      001        ZZZ       AAA           10-07-2017       105

      002        ZZZ       DDD          09-07-2017       103

      002        ZZZ       DDD           02-07-2017       113

      i need show distinct vendor count and every vendors max Fromdate price counts as below .

      1)Table1

      Material      Number of        Number of different

                        Vendors              Prices

      001             3                          4

      002             1                          1


      Dimension: Material

      Expression: 1)count({<Scope={'Yes'}>distinct Vendor})(For Number of Vendors)

                  2) How to write this expression to get Number of Different distinct latest Prices for each vendor )

      2)Table2

      In table2  i need to show latest Fromdate value and there respective distinct prices

      Material     vendor         From date       price

      001           AAA           27-6-2017        100 

      001           BBB           22-07-2017        20

      001           CCC           23-07-2017        10

      001           AAA           10-07-2017       105

      002           DDD           09-07-2017       103

       

       

      Thanks