# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save \$300 before February 6: REGISTER NOW!
cancel
Showing results for
Did you mean:
Partner - Contributor III

## Count Of fields Having Max ID

Hello one and all,

I have The following table

Sr No                Enquiry Id            Customer Id               Current Action

1                          ABC1                             AAA                                Not Interested

2                          ABC2                             BBB                               Order Placed

3                          ABC3                             BBB                                Material Supplied

4                          ABC4                             CCC                               Order Lost

5                          ABC5                             CCC                               AR Clear

6                          ABC6                             CCC                               Quotation Submitted

7                          ABC7                             DDD                               Interest Shown

8                          ABC8                             DDD                               Decision on Hold

9                          ABC9                             EEE                                Material Supplied

10                        ABC10                           FFF                                Negotiation Stage

11                        ABC11                            FFF                               Order Placed

12                        ABC12                           FFF                                Order Lost

13                        ABC13                           GGG                              Wrong Info

14                        ABC14                           GGG                              Not Interested

15                        ABC15                           HHH                               Product Info Requires

I want a Table As Follows:

Sr No                Enquiry Id            Customer Id               Current Action

1                          ABC1                             AAA                                Not Interested

2                          ABC3                             BBB                                Material Supplied

3                          ABC6                             CCC                               Quotation Submitted

4                          ABC8                             DDD                               Decision on Hold

5                          ABC9                             EEE                                Material Supplied

6                          ABC12                           FFF                                Order Lost

7                          ABC14                           GGG                              Not Interested

8                          ABC15                           HHH                               Product Info Requires

I just want the last current action to be displayed taking the Max Value of the Enquiry Id

Also let me know if a Bar Chart for the same can be created as well . I want the Dimension as Current Action and the Measure as Count(Distinct Customer Id) Where Enquiry Id to be max (Enquiry Id)

1 Solution

Accepted Solutions
Partner - Contributor III
Author

Hi

Actually It Shows the correct count when on display, but however when you are selecting a particular dimension, then the count increases because it even takes the count that doesn't have a max [Sr No].

For Example the Graph shows [Order Lost] count as 1, but if you click on it the count increases to 2. This is because it even takes [Customer Id] CCC's [Order Lost] into consideration. So I did the necessary change in the equation and then the answer comes correct

The correct Expression is as follows

Count({<"Sr No"={ \$(=Concat(aggr(max({1}"Sr No"),[Customer Id]),','))}>}"Customer Id")

19 Replies
MVP

try this

Count(If(Aggr(NODISTINCT Max(id), id) = dimension, id))

MVP

or this

max(aggr(count(CID), curentaction, ID))

Partner - Contributor III
Author

Doesn't work

Partner - Contributor III
Author

Even This Doesn't work

Contributor III

Hi,

datos:

Sr No,                Enquiry Id,            Customer Id,               Current Action

1,                    ABC1  ,                           AAA,                                Not Interested

2,                    ABC2  ,                           BBB,                               Order Placed

3,                    ABC3  ,                           BBB,                                Material Supplied

4,                    ABC4  ,                           CCC,                               Order Lost

5,                    ABC5  ,                           CCC,                               AR Clear

6,                    ABC6  ,                           CCC ,                              Quotation Submitted

7,                    ABC7 ,                            DDD ,                              Interest Shown

8,                    ABC8 ,                            DDD ,                              Decision on Hold

9,                    ABC9 ,                            EEE ,                               Material Supplied

10,                   ABC10,                           FFF  ,                              Negotiation Stage

11,                   ABC11,                            FFF ,                              Order Placed

12,                   ABC12,                           FFF  ,                              Order Lost

13,                   ABC13,                           GGG  ,                            Wrong Info

14,                   ABC14,                           GGG  ,                            Not Interested

15,                   ABC15,                           HHH   ,                            Product Info Requires

];

qualify *;

datos_mod:

Rowno() as [Sr No],

LastValue([Enquiry Id]) as [Enquiry Id],

[Customer Id],

LastValue([Current Action]) as [Current Action]

resident datos

group by [Customer Id];

Good luck!

Jose Luis

Partner - Master III

try making a group by in your script like below:

Rowno() as [Sr No],

[Customer Id],

max([Current Action]) as [Last Action],

max([Enquiry Id]) as [Enquiry Id]

group by [Customer Id];

you don't need to use INLINE.

Partner - Contributor III
Author

This is just for 15 Values, I have a table that has around 50000 rows of data

MVP

Hi

Try like this

Data:

LOAD *, RowNo() as RowID inline [

Sr No,                Enquiry Id,            Customer Id,               Current Action

1,                    ABC1  ,                           AAA,                                Not Interested

2,                    ABC2  ,                           BBB,                               Order Placed

3,                    ABC3  ,                           BBB,                                Material Supplied

4,                    ABC4  ,                           CCC,                               Order Lost

5,                    ABC5  ,                           CCC,                               AR Clear

6,                    ABC6  ,                           CCC ,                              Quotation Submitted

7,                    ABC7 ,                            DDD ,                              Interest Shown

8,                    ABC8 ,                            DDD ,                              Decision on Hold

9,                    ABC9 ,                            EEE ,                               Material Supplied

10,                   ABC10,                           FFF  ,                              Negotiation Stage

11,                   ABC11,                            FFF ,                              Order Placed

12,                   ABC12,                           FFF  ,                              Order Lost

13,                   ABC13,                           GGG  ,                            Wrong Info

14,                   ABC14,                           GGG  ,                            Not Interested

15,                   ABC15,                           HHH   ,                            Product Info Requires

];

Inner Join

LOAD [Customer Id], Max(RowID) as RowID Resident Data Group by [Customer Id];

Thanks & Regards,
Mayil Vahanan R