Crosstab Query - Fill in "some" null values only

giles_w1

New member
Local time
Today, 13:26
Joined
Aug 24, 2012
Messages
5
Hello All,
Hit a bit of a curly one this week. Hoping one of you clever people can help me.
I have created a crosstab query to display company sales data and managed to find a tip on the net about how to fill in blanks with Zero's (where we sold nothing that month). This works great however my report user now wants to change the report slightly so that when we launch new products, the months prior to launch don't display 0's but are left blank. Subsequent to 'launch' any months that have zero sales need to then display zero's.
So it would look like the below for a product introduced in April.
Month| Jan| Feb| Mar| Apr| May| June| July|
Product A| | |10| 15| 0| 18|
The current SQL of the query producing the report where all blanks are currently filled in with zero's is below.
Any ideas on how to make this work?
Thanks

TRANSFORM nz(Sum([X: Data Export DP Sales Qry].[Total Case Qty]))+0 AS [SumOfTotal Case Qty]

SELECT [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product, [X: Data Export DP Sales Qry].[SKU Description] AS description, [X: Data Export DP Sales Qry].[First Month of Sale]

FROM [X: Data Export DP Sales Qry]

GROUP BY [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product, [X: Data Export DP Sales Qry].[SKU Description], [X: Data Export DP Sales Qry].[First Month of Sale]

ORDER BY [X: Data Export DP Sales Qry].Owner, [X: Data Export DP Sales Qry].Brand, [X: Data Export DP Sales Qry].[Brand Type], [X: Data Export DP Sales Qry].[Sub Brand], [X: Data Export DP Sales Qry].[Customer Group], [X: Data Export DP Sales Qry].Product

PIVOT [X: Data Export DP Sales Qry].[MM-YYYY (Calendar)];
 
So you need three functions, Nz(), DateDiff() and IIF().

Nz() will "convert" Null to 0s.
DateDiff() will help you determine how far away the launch date is.
IIF() will help you write conditions and perform actions based on the outcome of the condition.
 
Thanks!

Not managing to make this work yet.

So to add some more info - within my query, I actually already have a field that shows the first month of sale, so I have been trying to use your advice but excluding the DateDiff logic, as I can just refer the IIF statement to the [First Month of Sale] shown in the query results.

However using the Nz and IIf together I still cannot make it work. I wonder if its because I am doing something wrong still.

Within the cross tab query, the column headings are months expressed as dates i.e. 01/01/2012, 01/02/2012, 01/03/2012

Where I struggle in the IIf statement is to know what to write regarding the month, as the Field for month 'was' [MM-YYYY (Calendar)] in the select query, but in the crosstab query this is no longer the case as such. The column headings are not the 01/01/2012 etc

I tried an IIf statement as below, but it didn't work. It still displayed ALL the 'null' crosstab values as 'null' rather than 'null' or 0.

SumOfTotal Case Qty: IIf([MM-YYYY (Calendar)]>=[First Month of Sale],nz(Sum([X: Data Export DP Sales Qry].[Total Case Qty]),0),"")

Any help is appreciated.
 
Alright, let's breakdown the process. First thing I want you to try is this for it to display all the months in the calendar:
Code:
PIVOT Format([X: Data Export DP Sales Qry].[MM-YYYY (Calendar)], "mmm") IN ('Jan', 'Feb', 'Mar', [COLOR=Red]...so on until Dec[/COLOR]);
... I'm assuming that [MM-YYYY (Calendar)] is a real Date/Time field.

Get this working then we will look at the Nulls.
 
Hi - so I have done that and it displays ok. However just to note, that the current MM-YYYY (Calendar) actually displays the date format I need - so it shows 01/01/2012 (Jan 2012), then 01/02/2012 (Feb 2012) etc. Each column in the query is a consolidated month. I need this to be the displayed result at the end. My data starts at 01/09/2009, and it currently up to 01/08/2012. 1 col per month.

Where to from here?

Thanks so much for helping.
 
If you want that format it can't be done manually like we've just done it. It will need to be done in code. But that's a pretty long list of columns... what will happen if you have dates ranging from 2009 to 2016? That's 6 * 12 = 36 columns. Are you happy with this?

Paste the SQL statement of the query as you have it now.
 
Hi there. I just checked with the user and they confirmed that the long list of columns IS indeed necessary. The SQL of the query is in the first thread above (08-24-2012, 02:17 PM). Are you able to use it? Thanks!
 

Users who are viewing this thread

Back
Top Bottom