# New to Qlik Sense

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

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for
Did you mean:
Creator

## How to modified my expression to get distinct count?

Hi guys, How should i modified my expression to ensure no duplicate for SR No. the value i want is 83, not the total of 0+A+B+C.

Formula to get Gate C:

sum(

Aggr (

if(

(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate C"}>

}(Gate))-count([SR No]))>0,

(Count ({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate C"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

-----------------------------------------------------------------------------------------------------------------------------------------------

Formula to get Gate 0+A+B+C:

(//B

sum(

Aggr (

if(

(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate B"}>

}(Gate))-count([SR No]))>0,

(Count ({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate B"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

+

//C

sum(

Aggr (

if(

(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate C"}>

}(Gate))-count([SR No]))>0,

(Count ({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate C"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

+

//0

sum(

Aggr (

if(

(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate 0"}>

}(Gate))-count([SR No]))>0,

(Count ({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate 0"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

+

//A

sum(

Aggr (

if(

(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate A"}>

}(Gate))-count([SR No]))>0,

(Count ({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>

*\$<[SR No]-={"''"}>

*\$<[Gate]={"Gate A"}>

} distinct(Gate))),

'No repetition') ,  [SR No]))

)

1 Solution

Accepted Solutions
MVP

Try this

Sum(Aggr(

RangeMax(//B

If(Count({\$<[SR No] = {"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate B"}>}Gate)-Count([SR No]) > 0,

Count({\$<[SR No] = {"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate B"}>} DISTINCT Gate), 'No repetition'),

//C

If(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate C"}>}Gate) - Count([SR No]) > 0,

Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate C"}>} DISTINCT Gate), 'No repetition'),

//0

If(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate 0"}>}Gate) - Count([SR No]) > 0,

Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate 0"}>} DISTINCT Gate), 'No repetition'),

//A

If(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}, [Gate]={"Gate A"}>} Gate) - Count([SR No]) > 0,

Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate A"}>} DISTINCT Gate), 'No repetition')

)

, [SR No]))

5 Replies
Specialist II

Hi,

if you want the count without repetition,

don't make addition, but just one expression with :

*\$<[Gate]={"Gate0","GateA","Gate B","GateC"}>

regards

MVP

I have no idea how to decipher your expression by just looking at the script and the image... would you be able to share a sample to check this out?

Creator
Author

Dear Sunny,

MVP

Try this

Sum(Aggr(

RangeMax(//B

If(Count({\$<[SR No] = {"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate B"}>}Gate)-Count([SR No]) > 0,

Count({\$<[SR No] = {"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate B"}>} DISTINCT Gate), 'No repetition'),

//C

If(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate C"}>}Gate) - Count([SR No]) > 0,

Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate C"}>} DISTINCT Gate), 'No repetition'),

//0

If(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate 0"}>}Gate) - Count([SR No]) > 0,

Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate 0"}>} DISTINCT Gate), 'No repetition'),

//A

If(Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}, [Gate]={"Gate A"}>} Gate) - Count([SR No]) > 0,

Count({\$<[SR No]={"=Min ({<Temp = {'Task_1'}>}[Date Created]) >=  MakeDate(2018,1,15)"}>*<[SR No]-={"''"}>*<[Gate]={"Gate A"}>} DISTINCT Gate), 'No repetition')

)

, [SR No]))

Creator
Author

Hi Sunny,

Now i know where i get it wrong. Already try to use rangemax before.

Thanks !!!

Tags
Community Browser