Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

andrzej12
New Contributor II

Qlik sense bar chart with expression on a measure not working as expected

Hello Qlik gurus,

On a measure in bar chart I have configured following expression:

Count({$<TYPENAME={'Workstation','Windows Workstation'}, RESOURCENAME={'ec-*'}, RESOURCENAME={'*-w', '*-ep', '*-eb'}, RESOURCESTATEID={'2'}, ACQUISITIONDATE-={'-'}>} Distinct RESOURCENAME)

I have thought that it will work like where clause from my query below:


select ... from ...

where typename in ('Workstation','Windows Workstation')

    and resourcename like 'ec-%'

    and (resourcename like '%-w' or resourcename like '%-ep' or resourcename like '%-eb')

    and resourcestateid=2

    and acquisitiondate is not null

My sql query in MS SQL gives me 5 records, Qlik measure gives 6 records.

I have checked and appeared that this one additional record calculated in qlik has RESOURCENAME='szk-1668-w'.

This record is not counted in sql query, because condition "...resourcename like 'ec-%' ..." is not fulfilled.

The strangest thing is when I change order of conditions in measure expression to the version below:

Count({$<TYPENAME={'Workstation','Windows Workstation'}, RESOURCENAME={'*-w', '*-ep', '*-eb'}, RESOURCENAME={'ec-*'}, RESOURCESTATEID={'2'}, ACQUISITIONDATE-={'-'}>} Distinct RESOURCENAME)

bar chart calculates 17 records !

I have double checked, in sets of records where there are only records like 'ec-%', results of calculations in qlik are OK.

Could someone tell me what is wrong in my expression for measure?

Why order of conditions has such significant impact on calculation?

Or maybe I look at the order of conditions like in sql query and it is wrong?

Any help appreciated.

1 Solution

Accepted Solutions
MVP
MVP

Re: Qlik sense bar chart with expression on a measure not working as expected

How about this

Count({$<TYPENAME = {'Workstation','Windows Workstation'}, RESOURCENAME = {'ec-*-w', 'ec-*-ep', 'ec-*-eb'}, RESOURCESTATEID = {'2'}, ACQUISITIONDATE -= {'-'}>} DISTINCT RESOURCENAME)

2 Replies
MVP
MVP

Re: Qlik sense bar chart with expression on a measure not working as expected

How about this

Count({$<TYPENAME = {'Workstation','Windows Workstation'}, RESOURCENAME = {'ec-*-w', 'ec-*-ep', 'ec-*-eb'}, RESOURCESTATEID = {'2'}, ACQUISITIONDATE -= {'-'}>} DISTINCT RESOURCENAME)

dwforest
Valued Contributor

Re: Qlik sense bar chart with expression on a measure not working as expected

Null values are not actually '-' that's just how they display, so can't test for '-'

use:

ACQUISITIONDATE = {*}


This indicates any value, so will exclude nulls.