I've got the attached list of dates, accounts, items, qty and costs. Not all items are present for all accounts on all dates, so I cannot (for instance) look on which dates there were zero sales for a particular item on a particular account (the data doesn't exist).
How can I generate rows with zero QTY and zero Cost where it does not exist? I know it's done with not Exists but I just dont seem to get it right. What happens if more dimensions are added (say region etc) - how should the script be adjusted.
It may be sufficient to add a master calendar to your model which includes all the dates from a minimum to a maximum date. The calendar should be kept in a separate table and associated with the transaction date. I prefer this to adding fake zero value transactions as these affect counts and averages.
There are many links here if you search for "master calendar".
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein