Variance In Crosstab (1 Viewer)

LB79

Registered User.
Local time
Today, 09:25
Joined
Oct 26, 2007
Messages
505
Hi All,

Im not having much luck with this - Ive tried DVar but maybe Im not understanding the function correctly.

I have a crosstab query that will only ever return 2 columns of data (sales for the current month and the previous month).

I want to add a column that will show the variance between these 2 columns. I can add a 'Total' column but can I add a variance column?

If not a built in function does anyone know of a custom function?

Many thanks
 

JHB

Have been here a while
Local time
Today, 10:25
Joined
Jun 17, 2012
Messages
7,732
What if you base a new query on your cross tab query and the calculate the DVar?
 

LB79

Registered User.
Local time
Today, 09:25
Joined
Oct 26, 2007
Messages
505
Thanks for the suggestion.
I had considdered this but the column names are dynamic so I cant include a set expression.
 

LB79

Registered User.
Local time
Today, 09:25
Joined
Oct 26, 2007
Messages
505
Is it possible to base an expression on the crosstab query column ID?
EG - I will only ever have 4 columns and I only ever want the variance of the last 2 columns.
If so, are the columns 0 based and how would I reference then in an expression?

Thanks
 

JHB

Have been here a while
Local time
Today, 10:25
Joined
Jun 17, 2012
Messages
7,732
Thanks for the suggestion.
I had considdered this but the column names are dynamic so I cant include a set expression.
Do you need to have the column names dynamic, (if not you give them a static name in the query)?
 

LB79

Registered User.
Local time
Today, 09:25
Joined
Oct 26, 2007
Messages
505
I tried to give column names in the Header property but then the data went to blank.
 

JHB

Have been here a while
Local time
Today, 10:25
Joined
Jun 17, 2012
Messages
7,732
Forget the Header property, you can do it in the QBE window like below:
[YourColumnName]: Col1
Or in the SQL string using the "AS [ColumnName]", (Renaming the first column from [Col1] to [YourColumnName]):
Code:
SELECT Col1 [B][COLOR=Red]AS [YourColumnName][/COLOR][/B]
FROM ColTable;
 

LB79

Registered User.
Local time
Today, 09:25
Joined
Oct 26, 2007
Messages
505
Sorry its not the actual column names I want to change.
I need to reference the columns that are the result of the crosstab.

In my crosstab I have 'Column Heading' = 'Mth'.
When the query runs it displays 2 Mth colomns (201408 and 201409 < these are months).
There will only ever be 2 months.
I want to calculate the difference betweern 201408 and 201409 (remembering the column names will be dynamic - showing the current month and the previous month).

I cant upload pictures as this site wants me to link the URL rather than upload.
 

JHB

Have been here a while
Local time
Today, 10:25
Joined
Jun 17, 2012
Messages
7,732
I don't know how your data is organized, but if you have something like below, you don't need a crosstab query to get the result.

The query: Input/Parameter to the query is "yyyymm" (ex. 201407)
PARAMETERS YearMonth Text ( 255 );
SELECT InputDataForExampleQuery.Describ, Sum((IIf(Format([TheDate],"yyyymm")=YearMonth,[AValue]))) AS Col1, Sum((IIf(Format([TheDate],"yyyymm")=YearMonth+1,[AValue]))) AS Col2, [Col2]/[Col1] AS Result
FROM InputDataForExampleQuery
GROUP BY InputDataForExampleQuery.Describ;
...
I cant upload pictures as this site wants me to link the URL rather than upload.
Scroll down a half page, then you'll see the button "Manage Attachments" click it.
 

Attachments

  • SampleData.jpg
    SampleData.jpg
    98.3 KB · Views: 887

Users who are viewing this thread

Top Bottom