Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
shekhar_analyti
Valued Contributor

What should be set expression to restrict dimension values one field but not in other field ?

Hi All ,

I have list of around 50 employee headed by 5 person . 

My requirement is to track sales ID by some 10 employee for any given  date range . 
But my set expression reduces the list of employee than needed . 

I  tried expression with pivot , but in vain 

1)  Count( DISTINCT {<TRANS={'B'},TYPE={'Music'},DATEE={">=$(=Date(eStartDate))<=$(=Date(eEndDate))"} >}ACTION_ID)

2) Count( DISTINCT {<NAME= {'Karole Peskett','Nikolaos Meedendorpe','Reg Andrzejak','Sigismondo Rudram','Antonius Beatens','Axel Este','Barny Chown'}, TRANS={'B'},TYPE={'Music'},DATEE={">=$(=Date(eStartDate))<=$(=Date(eEndDate))"} >}ACTION_ID)

With both  above expression , i could not get desired , below result 

data.PNG

 

Basically i need a set expression should give me the count of distinct ACTION_ID for given set of names against above Heads . 

Thanks & Regards

Shekar 

1 Solution

Accepted Solutions

Re: What should be set expression to restrict dimension values one field but not in other field ?

Try this expression

Count(DISTINCT {<NAME= {'Karole Peskett','Nikolaos Meedendorpe','Raviv O''Connell',
'Reg Andrzejak','Sigismondo Rudram','Antonius Beatens','Axel Este','Barny Chown'},
 TRANS={'B'},TYPE={'Music'},DATEE={">=$(=Date(eStartDate))<=$(=Date(eEndDate))"} >}ACTION_ID) +
Sum({<NAME= {'Karole Peskett','Nikolaos Meedendorpe','Raviv O''Connell',
'Reg Andrzejak','Sigismondo Rudram','Antonius Beatens','Axel Este','Barny Chown'}>} 0)
10 Replies
shekhar_analyti
Valued Contributor

Re: What should be set expression to restrict dimension values one field but not in other field ?

attached qvw 

shekhar_analyti
Valued Contributor

Re: What should be set expression to restrict dimension values one field but not in other field ?

@sunny_talwar  

Hi Sunny Bhai , please help with set expression 

shekhar_analyti
Valued Contributor

Re: What should be set expression to restrict dimension values one field but not in other field ?

@tresesco   ... Any help .. 

MVP
MVP

Re: What should be set expression to restrict dimension values one field but not in other field ?

May be just modifying the quotes like:

Remove the double quotes and add one more single quote as highlighted in red

Count( DISTINCT {<NAME= {'Karole Peskett','Nikolaos Meedendorpe','Raviv O''Connell',
'Reg Andrzejak','Sigismondo Rudram','Antonius Beatens','Axel Este','Barny Chown'},
TRANS={'B'},TYPE={'Music'},DATEE={">=$(=Date(eStartDate))<=$(=Date(eEndDate))"} >}ACTION_ID)
shekhar_analyti
Valued Contributor

Re: What should be set expression to restrict dimension values one field but not in other field ?

Hi Tresesco ,

Thanks for reply .

I tried your expresssion as well 

Count( DISTINCT {<NAME= {'Karole Peskett','Nikolaos Meedendorpe','Raviv O''Connell',
'Reg Andrzejak','Sigismondo Rudram','Antonius Beatens','Axel Este','Barny Chown'},
TRANS={'B'},TYPE={'Music'},DATEE={">=$(=Date(eStartDate))<=$(=Date(eEndDate))"} >}ACTION_ID)

 

But still its not working , out of 8 names from set expression only  5 is coming ..

dimesion restriction.png

 

Re: What should be set expression to restrict dimension values one field but not in other field ?

I guess that is because of the date range selected.... I changed the end date to be 02-05-2019 and I am seeing this now

image.png

shekhar_analyti
Valued Contributor

Re: What should be set expression to restrict dimension values one field but not in other field ?

Thank You Sunny Bhai for replying .


But my requirement is that , irrespective of date range for given set of Names , ACTION id count should be tracked .

Let say for a given date range there is no ACTION id for most of the given set of names  , then still my chart show show their names with null or 0 as expression values .  

I am not sure how to do it , even through i have explicitly given the names in set modifier 

shekhar_analyti
Valued Contributor

Re: What should be set expression to restrict dimension values one field but not in other field ?

dimesion restriction 2.png

Re: What should be set expression to restrict dimension values one field but not in other field ?

Try this expression

Count(DISTINCT {<NAME= {'Karole Peskett','Nikolaos Meedendorpe','Raviv O''Connell',
'Reg Andrzejak','Sigismondo Rudram','Antonius Beatens','Axel Este','Barny Chown'},
 TRANS={'B'},TYPE={'Music'},DATEE={">=$(=Date(eStartDate))<=$(=Date(eEndDate))"} >}ACTION_ID) +
Sum({<NAME= {'Karole Peskett','Nikolaos Meedendorpe','Raviv O''Connell',
'Reg Andrzejak','Sigismondo Rudram','Antonius Beatens','Axel Este','Barny Chown'}>} 0)