topic Re: Absolute Dates in Set Analysis in QlikView

Absolute Dates in Set Analysis

Fri, 21 Nov 2014 13:59:22 GMT

I'm trying to do a count of customers based on a particular date:

= count({<

Period_Date={"$(='31/01/2011')"}

>} CUSTOMER_ID)

The set analysis above doesn't work - I just get 0, could anyone give me some pointers please? Any help would be much appreciated

Re: Absolute Dates in Set Analysis

MK_QSL — Fri, 21 Nov 2014 14:04:27 GMT

COUNT({<Period_Date = {'31/01/2011'}>}CUSTOMER_ID)

COUNT({<Period_Date = {'31/01/2011'}>}DISTINCT CUSTOMER_ID)

Re: Absolute Dates in Set Analysis

alexandros17 — Fri, 21 Nov 2014 14:05:01 GMT

try

count({<Period_Date={'31/01/2011'}>} CUSTOMER_ID)

pay attention to date format that is period_date format must br DD/MM/YYYY

Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:14:50 GMT

Hi Manish and Alessandro

Thank you both for your suggestions, but that isn't working either for some reason (still getting 0 and I know I should be getting approx 140k...) - and I have checked that I'm using the correct date format.

Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:21:27 GMT

could you send a sample doc

Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:23:59 GMT

Hi Siobhan,

Is there potentially some other selections that are affecting you result? Try

count({1< Period_Date={'31/01/2011'}>} CUSTOMER_ID)

This will ignore all selections and just select that period date only

hope that helps

Joe

Re: Absolute Dates in Set Analysis

alexandros17 — Fri, 21 Nov 2014 14:26:32 GMT

I am sure the syntax is correct so try without set analysis

count(CUSTOMER_ID)

if it works (as I suppose try with

count({$ <Period_Date={'>01/01/2011'}>} CUSTOMER_ID)

count({$ <Period_Date={'>2011/01/01'}>} CUSTOMER_ID)

let me know

Re: Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:34:11 GMT

Hi Carly

I couldn't send the original app as it has some sensitive stuff in it, but here's a mock up - thanks for your help. Unfortunately I only have the free version of QV available to me at the moment so I can't open other people's files...

Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:43:16 GMT

It was how you spelt customer id, it looked right as the spelling was correct but the capitals cause it to error although it doesn't tell you, try this:

count({<Period_Date ={'31/01/2011'}>} Customer_ID)

Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:48:35 GMT

Hi Carly - it's not that, the spelling in the main app is CUSTOMER_ID, I just didn't write copy it properly in the mock up (sorry for the confusion)

Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:51:06 GMT

Thanks Joe - I have checked that and it doesn't seem to be the problem. I think I'm going to have to try and do this another way unfortunately

Re: Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:51:49 GMT

I only changed that and go it to work, see attached

Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 14:58:15 GMT

hmmm I see what you mean - it works in the mock up but not in my main app. There must be something else causing the problem, or else I've managed to corrupt my main app somehow

Thanks for your help

Re: Absolute Dates in Set Analysis

Fri, 21 Nov 2014 15:15:48 GMT

It is probably a comma or bracket out of place it gets me everytime.

Also select one date and then create a list box with this code =Period_Date this will confirm it is definately a date format in the background