topic Count between two dates in QlikView

Count between two dates

Anonymous — Fri, 20 Nov 2015 14:43:00 GMT

Hi all,

Can someone help me out with the below please.

count(

{

UNIT.STOCKSTATUS = {'Sold'},

UNIT.SOLD_DATE = {">=$(=max(UNIT.SOLD_DATE) -180) <=$(=max(UNIT.SOLD_DATE) -91)"},

BUYER.B_CATEGORY -={'PR'}

}

DISTINCT BUYER.B_NO)

What I am trying to do is count the buyer/customer is their maximum purchase date is more than the selected date -180 and less than the selected date -91.

Hope that makes sense, basically I'm trying to count buyers that are within a certain date range but I'm struggling and getting a zero result.

Thanks

Gareth

Re: Count between two dates

swuehl — Fri, 20 Nov 2015 15:06:41 GMT

Try to format the dates in the set modifier according the date format of UNIT.SOLD_DATE, like

UNIT.SOLD_DATE = {">=$(=Date(max(UNIT.SOLD_DATE) -180,'YYYY-MM-DD'))<=$(=Date(max(UNIT.SOLD_DATE) -91,'YYYY-MM-DD'))"}

Re: Count between two dates

Anonymous — Fri, 20 Nov 2015 15:10:04 GMT

Thanks Swuehl but no luck I'm afraid,

The below works fine and shows me buyers than have purchased more than the selected date - 90 days,

count(

UNIT.STOCKSTATUS = {'Sold'},

UNIT.SOLD_DATE = {">=$(=max(UNIT.SOLD_DATE)-90)"},

BUYER.B_CATEGORY = {'*'}-{'PR'},

UNIT.TOTALSOLD = {">=$(= 1 )"}

DISTINCT BUYER.B_NO)

I'm wanting to show between selected date -91 and selected date -180.

Any other ideas?

Thanks

Re: Count between two dates

swuehl — Fri, 20 Nov 2015 15:48:28 GMT

It would be easier if you could upload a small sample QVW.

You can try

count(

UNIT.STOCKSTATUS = {'Sold'},

UNIT.SOLD_DATE = {"=UNIT.SOLD_DATE >=$(=max(UNIT.SOLD_DATE)-180) and UNIT.SOLD_DATE <=$(=max(UNIT.SOLD_DATE)-91)"},

BUYER.B_CATEGORY = {'*'}-{'PR'},

UNIT.TOTALSOLD = {">=$(= 1 )"}

DISTINCT BUYER.B_NO)

Re: Count between two dates

Anonymous — Fri, 20 Nov 2015 16:01:50 GMT

Unfortunately I'm not able to upload anything,

That hasn't worked either, I thought it would have been quite straight forward, I'm just trying to cant the number of buyers between two dates.

Re: Count between two dates

swuehl — Fri, 20 Nov 2015 16:08:18 GMT

Hasn't worked either means a zero count result? Or something else?

Are you sure you have data records that fulfill the filter requirements and that UNIT.SOLD_DATE has a numeric representation, not only showing pure text values?

Re: Count between two dates

Anonymous — Fri, 20 Nov 2015 16:23:40 GMT

It's showing a zero value which isn't correct, I know I've got records that should be shown and I use UNIT.SOLD_DATE in various other pieces on my dashboard.

I think I may know why, I'm selecting a month.

What I'm wanting to do is to be able to select a month and then show the number of buyers that have bought between 180 and 90 days before that selected month. But as the month is selected I'm guessing this is stopping the set analysis working? Would I need to use P()?

Feel free to correct me if I'm completely wrong!

Thanks

Re: Count between two dates

Anonymous — Fri, 20 Nov 2015 16:44:18 GMT

Another idea I had was the below but that isn't working either,

count(

{

UNIT.STOCKSTATUS = {'Sold'},

UNIT.SOLD_MONTH = {">=$(=max(UNIT.SOLD_DATE)-180) <=$(=max(UNIT.SOLD_DATE)-91)"},

BUYER.B_CATEGORY = {'*'}-{'PR'}

UNIT.STOCKSTATUS = {'Sold'},

UNIT.SOLD_MONTH = {">=$(=max(UNIT.SOLD_DATE)-90)"},

BUYER.B_CATEGORY = {'*'}-{'PR'}

}

DISTINCT BUYER.B_NO)

Re: Count between two dates

swuehl — Fri, 20 Nov 2015 16:50:11 GMT

If you make selection in other calendar fields, you need to clear these fields in set analysis to avoid incompatible sets:

count(

{

UNIT.STOCKSTATUS = {'Sold'},

UNIT.SOLD_DATE = {">=$(=max(UNIT.SOLD_DATE) -180) <=$(=max(UNIT.SOLD_DATE) -91)"},

BUYER.B_CATEGORY -={'PR'},

MONTHFIELD= ,

WEEKFIELD=

}

DISTINCT BUYER.B_NO)

You may still need to check the set modifier for UNIT.SOLD_DATE, but you must clear the other calendar fields.

Re: Count between two dates

Anonymous — Mon, 23 Nov 2015 10:23:40 GMT

You're a genius Swuehl! It works!

Thank you,

Gareth