Count between two dates

[Anonymous]

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

[swuehl]

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'))"}

[Anonymous]

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

[swuehl]

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)

[Anonymous]

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.

[swuehl]

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?

[Anonymous]

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

[Anonymous]

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)

[swuehl · Accepted solution]

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.

[Anonymous]

You're a genius Swuehl! It works!

Thank you,

Gareth