Show records in pivot table based on condition

Tue, 12 Apr 2016 13:17:11 GMT

Hi there

I am trying to find sales transactions that show TVs sold but with NO TV license. I just have 1 sales table in my model showing sales of TVs and TV licenses (indicated by SaleType). I tried this using a straight table, see below, but then discovered that there was in fact a TV license sold, but it shows on a different line which doesn’t really help me. So the ‘No TV Lic’ shown below in the calculated dimension would be wrong.

I then used a pivot table showing sales of TVs and TV licenses per sales doc (Dimensions are SalesDoc and SaleType).

3 questions:

• Is there a better way of showing this apart from the pivot table below?

• If not, how do I show only Sales docs where there was a TV sold but no TV license? I.e. where TV Article = 1 and TV License is null.

• And then how would I show the count of this and percentage that this count is of total TVs sold?

Re: Show records in pivot table based on condition

Gysbert_Wassenaar — Wed, 13 Apr 2016 07:24:15 GMT

I don't think you should use a calculated dimension. Instead change your chart expressions so they only calculate results for SalesDoc values that do not have a tv license:

count({<SalesDoc=E({<TVLicenseNumber={'*'}>}SalesDoc)>} distinct SalesDoc)

topic Re: Show records in pivot table based on condition in QlikView

Show records in pivot table based on condition

Re: Show records in pivot table based on condition