topic Re: Dates in set analysis in QlikView

Dates in set analysis

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

Hi All,

I have requirement where in I have to get the count of a dimesion w.r.t to one date based on the selection of a different date dimension

Please find the sample data

Customer	Effective date	Expiration date
A	6/1/2017	6/1/2018
B	7/2/2017	7/2/2018
C	6/3/2017	6/3/2018
D	8/4/2017	8/4/2018
E	8/5/2016	8/5/2017
F	8/5/2017	8/5/2018
G	8/12/2017	8/12/2018

now we have the month and year filter selections based on effective date.

So the requirement is when we click on effective date we should get count of how many customers are having effective date as well as how are expiring.

for eg: if we click on Aug 2017 we should get count as 1 as we have customer E expiring in aug 2017 and another test box with count of customers having aug 2017 as effective date which will 3

Month and year filter based on effective dates.

So we have two text boxes

Count of customers expiring count of customer effective

Kindly let me know if requirement is unclear

Thanks,

anupama jagan

Re: Dates in set analysis

maxgro — Sun, 17 Dec 2017 20:05:35 GMT

Look at the attachment

Re: Dates in set analysis

Anonymous — Sun, 17 Dec 2017 20:23:41 GMT

Thanks a lot for your solution. It works great !!. Just a small doubt Is it possible to show count(customer) effective (in expiring customers text box) when no selections are made?

Re: Dates in set analysis

maxgro — Sun, 17 Dec 2017 20:37:06 GMT

You can use the max function to count the customer of the max year and max month.

count({$ <eff_year=,eff_month=,eff_date=,exp_date={">=$(=date(monthstart(makedate(max(eff_year),max(eff_month)))))<=$(=date(monthend(makedate(max(eff_year),max(eff_month)))))"}>} DISTINCT customer)

count({$ <eff_year=,eff_month=,eff_date={">=$(=date(monthstart(makedate(max(eff_year),max(eff_month)))))<=$(=date(monthend(makedate(max(eff_year),max(eff_month)))))"}>} DISTINCT customer)

Re: Dates in set analysis

mariusz_kumansk — Sun, 17 Dec 2017 21:41:37 GMT

HI, Like this? you should think about creating Master Calendar in your application.

Re: Dates in set analysis

Anonymous — Thu, 21 Dec 2017 16:46:39 GMT

Hi Massimo,

Is it possible to get ytd and qtd for this?

count({$ <eff_year=,eff_month=,eff_date=,exp_date={">=$(=date(monthstart(makedate(max(eff_year),max(eff_month)))))<=$(=date(monthend(makedate(max(eff_year),max(eff_month)))))"}>} DISTINCT customer)

Re: Dates in set analysis

maxgro — Thu, 21 Dec 2017 17:55:28 GMT

try wity

yearstart or quarterstart

today - 1

Re: Dates in set analysis

Anonymous — Wed, 17 Jan 2018 09:52:34 GMT

Hi Massimo,

the expression is working well when we select single year and month, is it possible to extend this one for multiple selections?