Advanced set analysis

[PapaJapa]

Hello.

I analyze sales on ebay auctions. I have an auction list and a sale on them (by days [SalesDate]). Each auction has a start and end date (AuctionEndDate). I want to count auctions completed in a month that had sales in the last 7 days before the end.

I want to get a simple table with two columns:

1. month([AuctionEndDate]) and it is ok
2. count of auctions with sales in last 7 days

I tried with 

count(DISTINCT {<[AuctionID] = {"=Sum({<[SalesDate]={">$(=date([AuctionEndDate]-7))"}>}[Sales])>'0' "}>} [AuctionID])

... but without success.

Please help 🙂

Thanks,

M

[chris_djih]

May i suggest to shift this problem into the script:

Just create a new Fields:

• If(AuctionEndDate>=Date(Today()-7),1,0) as last7Days_Flag

then you can transform your Set-Analysis: Count(distinct {<last7Days_Flag={1},Sales={">0"}>} AuctionID)