1 Reply Latest reply: May 25, 2018 6:02 PM by David Forest RSS

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

    Andrzej Kisielewicz

      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.