Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kunkumnaveen
Specialist
Specialist

how to get distinct count

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

7 Replies
kunkumnaveen
Specialist
Specialist
Author

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

May be this?

Capture.PNG

kunkumnaveen
Specialist
Specialist
Author

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
Master III
Master III

For this try like this

kunkumnaveen
Specialist
Specialist
Author

HI

could u plz paste expression ....

prma7799
Master III
Master III

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

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

sorted.png

tresesco
MVP
MVP

Dimension: Material

Exp1: Count( Distinct Vendor)

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