Date query

rosaW

New Member
Hello,

I have created a query to show on which week numbers of the year, customers have bought products. I now want to show the week numbers in which each customer has not bought anything.
Can anyone explain how to do this?

Greatly Appreciated!

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

tjlindow

New Member
Hi Rosa,

Can you post a screen shot with some made-up data? It's difficult to propose a solution without having an idea of how the source data looks.

TJL

tjlindow

New Member
Rosa,

The easiest solution to see the weeks where a customer did not buy would be to create a pivot table with strCustomerID in the rows, dtm Year and dtmWeek in the columns, and count on dtmWeek. Blank values in the resulting pivot table would indicate weeks where the customer did not buy; a value of 1 would show the customer did make a purchase.

TJL

rosaW

New Member

Thank you that worked! Now i only need to show the customers with the weeks where there are blank values or did not purchase. How would i set up a filter for this?

tjlindow

New Member
First, you would need to remove the Grand Totals for columns and rows. Then you could add a formula in a cell that is well to the right of the pivot table (so that the formula isn't immediately overwritten by the Pivot Table when additional data is added). If you choose to put the filter formula in Column AA, the formula would be:

=SUM(B\$4:Z4)-COUNTA(\$B\$3:Z\$3)

This formula assumes your column labels (dates) are in row 3 and your first column of pivot data is in B4. "0" values for the formula would imply that the customer has made purchases in each week - you could filter these out. Values that are >0 mean that the customer has missed one or more weeks.

TJL

rosaW

New Member

I don't know if this makes a difference or if you realized this, that i am using MS Access.

rosaW

New Member
Indeed. I see that now..i'm new to this site. Thanks!

Replies
0
Views
143
Replies
1
Views
60
Replies
7
Views
340
Replies
3
Views
273
Replies
0
Views
115

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,979
Messages
5,767,432
Members
425,412
Latest member
andrealp4444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back