topic Re: find missing customer ids in QlikView

find missing customer ids

gauthamchilled — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I'm attaching a sample app.

My requirement is if i select particular year and see the sales in the chart, i want to see the missing customers by comparing previous year clients and show this in the chart.

Anyone pleas ehelp?

Re: find missing customer ids

stabben23 — Tue, 07 Feb 2017 07:08:13 GMT

Hi Gautham, you can simply do a pivot table instead of a straight table. Then drag and drop the Period Dimension on top.

Re: find missing customer ids

gauthamchilled — Tue, 07 Feb 2017 07:22:51 GMT

what i want is

in a chart for year selected 2016,

what are the customers that exist in 2015 but not available in 2016.

Re: find missing customer ids

stabben23 — Tue, 07 Feb 2017 07:28:18 GMT

Ok, then you need set analysis in Your Expression, maybe like this, remove Period in Your Dimension.

Sum({<Period={$(=max(Period))}>}Sales) for selected period (2016)

Sum({<Period={$(=max(Period)-1)}>}Sales) for selected -1 period (2015)

Re: find missing customer ids

gauthamchilled — Tue, 07 Feb 2017 08:17:42 GMT

your 1st expression gives customer sales for only 2016, second expression gives for only 2015.

how can i get the customers that exist in 2015 but not available in 2016.

Re: find missing customer ids

tresB — Tue, 07 Feb 2017 08:29:03 GMT

Try like:

For - Present in 2015 and not in 2016 :

Sum({<CustomerId=p({<Period={'$(=max(Period)-1)'}>})>-<CustomerId=p({<Period={'$(=max(Period))'}>})>}Sales)

For - Present in 2016 and not in 2015 :

Sum({<CustomerId=p({<Period={'$(=max(Period))'}>})>-<CustomerId=p({<Period={'$(=max(Period)-1)'}>})>}Sales)

Re: find missing customer ids

gauthamchilled — Wed, 08 Feb 2017 05:53:39 GMT

hi Tresesco, 2nd expression works but not 1st one. can you please help?

Re: find missing customer ids

tresB — Wed, 08 Feb 2017 06:53:43 GMT

First works too. Only thing is - there are no customer ids that are present in 2015 and not in 2016, because 2016 is a super-set here in your sample. Therefore you don't get anything to see as output.