Need your expertise to solve this problem :p

If you have a suggestion that includes other types data then plz let me know too

Hi David

Can you modify the query (or create a new one) to pick up the last 4 quarters only, rather than linking to specific quarters? If the quarters are linked to a unique incremental value (e.g. last date of the quarter, of if the quarter was formatted yymm or even an autonumber for example) then you could very easily detemine the last non-null quarter with a max query. For the quarterly report showing the last 4 quarters, you would then select all quarters greater than the max quarter less 4.

HTH, Andrew.

I will try to show the setup by pasting the SQL code (if possible):

My Store-database consist of the following information (Info in () are the danish names :D):

Year (År) - StoreID (ButiksID) - Storename (Butiksnavn) - Revenue (Omsætning kr) - Quarter (Kvartal)

To retrieve the data as I want I start by making a cross-table (Think You recommended that) which has the following setup:

TRANSFORM Sum(Butiksdata.[Omsætning kr]) AS [SumOfOmsætning kr]
SELECT Butiksdata.ButiksID, Butiksdata.Butiksnavn, Sum(Butiksdata.[Omsætning kr]) AS [Total Of Omsætning kr]
FROM Butiksdata
GROUP BY Butiksdata.ButiksID, Butiksdata.Butiksnavn
PIVOT Butiksdata.Kvartal;

I create another query from which I retrieve data from the cross-table. This is done to enable me to do two things. First option, to be able to sort and get a Top 250 list, and secondly, being able to make a report which shows the quarterly revenue horizontally. The SQL code for that query looks like this:

SELECT TOP 250 test.ButiksID, test.Butiksnavn, Sum(test.Q2) AS [Total Q2], Sum(test.Q1) AS [Total Q1], Sum(test.Q4) AS [Total Q4], Sum(test.Q3) AS [Total Q3], Sum([q2]+[q1]+[q4]+[q3]) AS Total, Kædetabel.Kæde
FROM test INNER JOIN Kædetabel ON test.ButiksID = Kædetabel.ButiksID
GROUP BY test.ButiksID, test.Butiksnavn, Kædetabel.Kæde
ORDER BY Sum([q2]+[q1]+[q4]+[q3]) DESC;

I then make a report which gives me the result for now, but my problem is - as You can see, that I don't know what to do to show only 4 quarters at a time.

So this is the data I have. Can You explain yourself more in detail Andrew?

andrew93 said:

Hi David

Can you modify the query (or create a new one) to pick up the last 4 quarters only, rather than linking to specific quarters? If the quarters are linked to a unique incremental value (e.g. last date of the quarter, of if the quarter was formatted yymm or even an autonumber for example) then you could very easily detemine the last non-null quarter with a max query. For the quarterly report showing the last 4 quarters, you would then select all quarters greater than the max quarter less 4.

HTH, Andrew.

Click to expand...

To retrieve the data as I want I start by making a cross-table (Think You recommended that) which has the following setup:

TRANSFORM Sum(Butiksdata.[Omsætning kr]) AS [SumOfOmsætning kr]
SELECT Butiksdata.ButiksID, Butiksdata.Butiksnavn, Sum(Butiksdata.[Omsætning kr]) AS [Total Of Omsætning kr]
FROM Butiksdata
GROUP BY Butiksdata.ButiksID, Butiksdata.Butiksnavn
PIVOT Butiksdata.Kvartal;

Click to expand...

Hi David

It can be difficult giving advice without seeing the data structures - if I provide advice about something and help to solve a problem then there is always the possibility of it causing a problem somewhere else (as has happened).



Is there any way I can see a sample of the imported data and the data structures in your database?

How is the "Kvartal" getting into the "Butiksdata" table? Is it part of the import file or is it something that you assign after the import? Also, do you have any tables with the details of the quarters? (e.g. a table with the quarter, a description, the first date of the quarter, last date of the quarter etc.?)

In your crosstab query, under the "PIVOT Butiksdata.Kvartal" - you want to set some criteria to limit the quarters to the last 4 quarters. However, we can't say something like >0403 or >=[Select first quarter] etc because if the quarter is stored as a number then 0104 should be included but in fact 104 is less than 403 and won't get included (so it won't work). However, something like that would work if the quarters were stored in the yymm format (i.e. 0401 would be larger than 0304, 0303 and 0302).

If the quarter is stored as text then it will be difficult to compare values (although it can be done). However, if you wish to retain the current layout and formatting for the quarter, could the quarter be identified in some other way? This is what I was attempting to say in my previous post - if we could identify the quarter through a unique incremental number (e.g. last day of the quarter etc.) then it would be a lot easier. Any such value would not have to be visible in the final output or report but could be used for setting the criteria to help you get the correct quarters onto the report.

However, a clunky way of achieving this is to enter something like the following into the criteria : =[Enter Q1 id] or =[Enter Q2 id] or =[Enter Q3 id] or =[Enter Q4 id]. I personally would prefer to see a more elegant solution to your problem but it is difficult without seeing your database.

Andrew.

It can be difficult giving advice without seeing the data structures - if I provide advice about something and help to solve a problem then there is always the possibility of it causing a problem somewhere else (as has happened).



Is there any way I can see a sample of the imported data and the data structures in your database?

Click to expand...

Yes, but I don't know how to make that nice screenshoot you make from time to time. But as I said in my previous post my structure is rather simple

Year - StoreID - Storename - Revenue - Quarter
2003 - 1002 - Irma - 4854 - Q3
2003 - 1002 - Irma - 2356 - Q4

How is the "Kvartal" getting into the "Butiksdata" table? Is it part of the import file or is it something that you assign after the import? Also, do you have any tables with the details of the quarters? (e.g. a table with the quarter, a description, the first date of the quarter, last date of the quarter etc.?)

Click to expand...

The Quarter is something I entered before adding the data to the database. I hoped to be able to store the data for each Storechain in it's own table, but I can't seem to find a way to combine them in a query. So I choose to edit data before I enter it into my database and in the process add the quarters, but it's not very easy a process. Anyway, I guess what I say is that I enter the quarters semiautomatic in excel, so I can ofcourse use numbers to indicate the quarter.

In your crosstab query, under the "PIVOT Butiksdata.Kvartal" - you want to set some criteria to limit the quarters to the last 4 quarters. However, we can't say something like >0403 or >=[Select first quarter] etc because if the quarter is stored as a number then 0104 should be included but in fact 104 is less than 403 and won't get included (so it won't work). However, something like that would work if the quarters were stored in the yymm format (i.e. 0401 would be larger than 0304, 0303 and 0302).

Click to expand...

That seems as a good idea, but how do I make it automatic so that I can concentrate on entering data?

It wasn't much data I gave you but it's rather simple data structure imho but more complicated to handle than I thought

Hi David

I now understand the problem more. In your crosstab query change your row heading from "Quarter" to this :

Period : [Year] & Right([Quarter],1) - using your variable names of course

Then in the criteria section for the row heading enter something like this :

>=[Enter first quarter (yyyyq)]

When you open the query you will be asked to "Enter first quarter (yyyyq)" and if you enter 20033 then you will get all of the quarterly data for Q3, 2003 and greater.

If you can get this to work then it is a start and from there we should be able to figure out a better way of writing the criteria to pick up four quarters only.

HTH, Andrew.

Then in the criteria section for the row heading enter something like this :

>=[Enter first quarter (yyyyq)]

Click to expand...

My apologies for not fully testing this, it doesn't work as expected.



Please ignore the bit that I have quoted above and once you have changed the row heading per my previous post, use this instead :

Crosstab Query Design View, Menu -> Query -> Parameters -> Enter first quarter (yyyyq) -> Integer -> OK.

Criteria for row heading :

>=[Enter first quarter (yyyyq)] and <=[Enter first quarter (yyyyq)]+9

This will give you the 4 quarters starting from the one you enter (enter it in the format yyyyq e.g. 20033).

{Please ignore if too confusing : Alternatively, you could turn the criteria around by asking for the last quarter and changing all of the "first" variable names to "last" (including the bit under Query -> Parameters) and use something like >=[Enter last quarter (yyyyq)]-9 and <=[Enter last quarter (yyyyq)] instead }

Andrew.

Sorry for the late answer...Just had otherm tasks to finish and tx for all Your help

Ok I now made a query that included the formula above, So my cross_tab now have 20033, 20034, 20041, 20042 as quarters

I then build a new query from that cross_tab which again enables me to make the report that is required.

But the part below I don't get. Or can't make it work

Shouldn't I be able to make a formula that only looks at the quarters in this query:

SELECT Butiksdata.År, Butiksdata.ButiksID, Kædetabel.Kæde, Butiksdata.Butiksnavn, Butiksdata.[Omsætning kr], Butiksdata.Kvartal, [år] & Right([Kvartal],1) AS Period
FROM Butiksdata INNER JOIN Kædetabel ON Butiksdata.ButiksID = Kædetabel.ButiksID
GROUP BY Butiksdata.År, Butiksdata.ButiksID, Kædetabel.Kæde, Butiksdata.Butiksnavn, Butiksdata.[Omsætning kr], Butiksdata.Kvartal, [år] & Right([Kvartal],1);

And then only looks at the four last quarters?

I know...I'm not good at this

andrew93 said:

Then in the criteria section for the row heading enter something like this :

>=[Enter first quarter (yyyyq)]

Click to expand...

My apologies for not fully testing this, it doesn't work as expected.



Please ignore the bit that I have quoted above and once you have changed the row heading per my previous post, use this instead :

Crosstab Query Design View, Menu -> Query -> Parameters -> Enter first quarter (yyyyq) -> Integer -> OK.

Criteria for row heading :

>=[Enter first quarter (yyyyq)] and <=[Enter first quarter (yyyyq)]+9

This will give you the 4 quarters starting from the one you enter (enter it in the format yyyyq e.g. 20033).

{Please ignore if too confusing : Alternatively, you could turn the criteria around by asking for the last quarter and changing all of the "first" variable names to "last" (including the bit under Query -> Parameters) and use something like >=[Enter last quarter (yyyyq)]-9 and <=[Enter last quarter (yyyyq)] instead }

Andrew.

Click to expand...

Hi David,

Sorry for the late reply (different time zones plus I have been busy).

I don't fully understand this bit :

I then build a new query from that cross_tab which again enables me to make the report that is required.

Click to expand...

Is the new query a copy of the first query but saved as something else?
Or is the new query sourcing it's data from the first query?

And if you could give a few more details for this bit please :

But the part below I don't get. Or can't make it work

Click to expand...

In what way is it not working?

If this new query is a copy of the other query then I think the "ar" field will make each store have 2 rows on the report - 1 for each year.

Or if the new query is sourcing it's data from the old query then the "kvartal" variable will no longer be available as a variable (because the variable names will change to something like 20033 or 20034 etc.).

Cheers, Andrew.