Hi all!
I have a table with some amounts, a date and a region. I'm building a crosstab query to display a the date (consolidated into quarters) as row headings (so four rows Q1, Q2, Q3 and Q4) and region as column heading and of course the amount as value.
That works perfectly, only I want to add a Where clause on the date, so that I can look at q1-q4 of a single year. Here's the problem: Some years they may not be any records in a specific quarter, so I end up having for instance only rows for Q2 and Q4. I'm using the column headings functionality, but is there a cool way to make sure, it will also show Q1 and Q3 with just blanks or zeroes? - I tried a UNION, but that won't work in a crosstab (as expected).
I have a table with some amounts, a date and a region. I'm building a crosstab query to display a the date (consolidated into quarters) as row headings (so four rows Q1, Q2, Q3 and Q4) and region as column heading and of course the amount as value.
That works perfectly, only I want to add a Where clause on the date, so that I can look at q1-q4 of a single year. Here's the problem: Some years they may not be any records in a specific quarter, so I end up having for instance only rows for Q2 and Q4. I'm using the column headings functionality, but is there a cool way to make sure, it will also show Q1 and Q3 with just blanks or zeroes? - I tried a UNION, but that won't work in a crosstab (as expected).