Skip to main content

New to Qlik Sense

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

Announcements
Nov. 30th, Webinar! Do More With Qlik Cloud Catalog & Lineage: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
stuhico09
Creator
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]))

)

ass.PNG

stalwar1‌ please help. Thanks.

1 Solution

Accepted Solutions
sunny_talwar

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]))

View solution in original post

5 Replies
ogautier62
Specialist II
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

sunny_talwar

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?

stuhico09
Creator
Creator
Author

Dear Sunny,

Please check. Thanks.

stalwar1

sunny_talwar

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]))

stuhico09
Creator
Creator
Author

Hi Sunny,

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

Thanks !!!