How to select data in measurement?

[ivanyang25]

Dear QS Experts

I am new to Qlik Sense and I have a question about how to select data in measurement

I have a simple pivot table as below.

As each 'Sales No' may have more than one order line thus purpose of this pivot table is to calculate the order line of each 'Sales No'

Now I want to select 'Sales No' which order line quantity is more than 1, but I can't directly select them in measurement. May I know how to make it happen?

Thank you for your advise in advance

Ivan

[arvind_patil]

Hi lvan,

I think you can write in expression in that way:

Count({<SalesNO={'=Count(OrderNo)>1'}>}OrderNo)

Thanks,

Arvind Patil

[arvind_patil · Accepted solution]

HI lvan,

Your table is like this:

Table1:

LOAD * Inline [

SalesNO, OrderNo

1, 11

1,12

2,21

2,22

3,31

4,41

5,51

5,52

5,53

];

Output:

Count({<SalesNO={'=Count(OrderNo)>1'}>}OrderNo)

Thanks,

Arvind Patil

[ivanyang25]

Patil

Thank you so much for you prompt request

I am sorry that I have another question accordingly

I create another measurement which simply minus 1 from the previous measurement

However, the total order line also simply minus 1 as well. The total sum order line of "N-1" should be 2776

May I know which part is wrong?

Ivan

[arvind_patil]

Hi lvan,

I hope it will help to solve your problem:

Count({<SalesNO={'=Count(OrderNo)>1'}>}OrderNo )-1

Try this:

Thanks,

Arvind Patil

[arvind_patil]

Hi Ivan,

If it is helpful for you then mark it .

Thanks,

Arvind Patil

[ivanyang25]

Patil

Thank you for your reply

As you can see the sum total of 'N-1' should be 4 (1+1+2). However, the total number in the first line is 6

May I know how could we achieve it?  Thanks

Ivan