how to get distinct count

[kunkumnaveen]

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

[kunkumnaveen]

2)Table2

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

Material  Plant       vendor         From date       price

001         XXX        AAA           27-6-2017        100 

001         XXX        BBB           22-07-2017        20

001         XXX        CCC           23-07-2017        10

001         YYY        AAA           10-07-2017       105

002         ZZZ        DDD           09-07-2017       103

[tresesco]

May be this?

[kunkumnaveen]

Thanks for your reply

Material      Number of        Number of different

                  Vendors              Prices

001             3                          4

002             1                          1

if i need show count then is it possible to write count expression in firstsortvalue

[prma7799]

For this try like this

[kunkumnaveen]

HI

could u plz paste expression ....

[prma7799]

1.count(DISTINCT{<Fromdate = {"=max(Fromdate)"}>}Vendor)

2.count(aggr(count(Price),Material,Vendor,Plant))

[tresesco]

Dimension: Material

Exp1: Count( Distinct Vendor)

Exp2: Aggr( Count( TOTAL  <Material> DISTINCT Plant&'|'&Vendor), Material, Plant)