14 Replies Latest reply: Apr 3, 2017 2:16 PM by Ananya Kushary

# QlikSense

I have a distinct count of records. When i include a second dimension(status), the distinct count includes the same record for 2 different status as 2 records(i would only like to consider it once, with the latest updated status).

Please can anyone help with the calculation.

Thanks

• ###### Re: QlikSense
• Hi,

Try like this,

=Aggr(count(DISTINCT OrderID),status)

Thanks,

Deva

• ###### Re: QlikSense

Hi Ananya kushary,

Can you please post the example qvf, we will bring the output.

Thanks,

• ###### Re: QlikSense

Here is the data:

Order No      Status                   Date

1                  Complete             03/23/2017

1                  Pending               03/22/2017

2                  In Progress          03/25/2017

2                  Pending               03/21/2017

3                  Rejected              03/22/2017

3                  In Progress          03/21/2017

My bar graph with Order No should give me distinct count =3

Also i want the graph to have a second dimension (Status of the max date). So graph should show:

1- Complete, 1-in Progress, 1-Rejected

Currently : it shows me distinct count 3, but when i include status it shows me 6 (which is incorrect).

Hope this explains

• ###### Re: QlikSense

Can you attach an image of what you curretly have (your bar chart) and you want to do with it?

• ###### Re: QlikSense

If I fully understand you, this is your source:

Order No,      Status, Date

1,                  Complete,            03/23/2017

1,                Pending ,              03/22/2017

2,                  In Progress ,        03/25/2017

2,                  Pending,03/21/2017

3,                  Rejected ,            03/22/2017

3,                In Progress ,        03/21/2017

];

And this is the desired output?

The Status bu Order No for the max Date?

See the attached App:

• ###### Re: QlikSense

Yeah that's quite correct.

i have attached two screenshots of my bar chart

1)the first one has one dim (BU) and distinct count of order no (4)

2)the same chart when i add a second dimension (status) shows a distinct count of 6

I would want only 4 to be displayed with the status of those 4 records (status of max date of "Update date")

Dont know if this makes sense.

• ###### Re: QlikSense

Hi Ananya,

What's this value LENEL and the dimension BU?

Can you alter your source table :

Order No      Status                   Date

1                  Complete             03/23/2017

1                  Pending               03/22/2017

2                  In Progress          03/25/2017

2                  Pending               03/21/2017

3                  Rejected              03/22/2017

3                  In Progress          03/21/2017

So I can figure out what you're willing to do?

And please don't forget to mark as helpful the answers that have been helpful to you (under action, button left of each message) and as correct the correct answer.

Omar,

• ###### Re: QlikSense

yeah i just had one scenario , so consider all these records of BU: Lenel

• ###### Re: QlikSense

Like this?

BU,   Order No      Status                   Date

Lenel,   1                  Complete             03/23/2017

Lenel,   1                  Pending               03/22/2017

Lenel,   2                  In Progress          03/25/2017

Lenel,    2                   Pending               03/21/2017

Lenel,   3                  Rejected              03/22/2017

Lenel,   3                  In Progress          03/21/2017

• ###### Re: QlikSense

yes

• ###### Re: QlikSense

Is this ok?

Check the attached App

• ###### Re: QlikSense

Yeah this is how i want the bar to be stacked with the status and distinct count.

What have you used in  the measure field?

• ###### Re: QlikSense

=Aggr( max(DateStatus),[Order No])

Check the attached App, you'll find out.

If you don't understand a thing, don't hesitate to 'call back'.

Have a nice weekend

• ###### Re: QlikSense

Hi,

Thanks a ton for your help.

There seems to be some error when i tried this approach you suggested.

I tried for some more and this below formula worked for me

Count(If(Aggr(NODISTINCT Max(Date), [Order No]) = Date, [Order No]))  as the measure formula