Xcelsius and Proper Use of QaaWS

system · August 27, 2008, 12:04am

Hi everyone,

I’m using Xcelsius 2008 w/ QaaWS, and have a query about proper usage, specifically with selectors.

I have three columns of data, Year, Quarter and Sales (from eFashion of course!) =)

Yr Qtr Sales
== === ====
01 Q1 100
01 Q2 200
02 Q1 500
02 Q2 70

I want to put this data into a cross-tab format within the Excel spreadsheet, so that I can use row selectors to make the chart dynamic. So the data would look like this:

 Q1 Q2

01 100 200
02 500 70

Can anyone describe how to do this with QaaWS easily?

Thanks

josh.fletcher (BOB member since 2007-01-08)

system · August 27, 2008, 2:22pm

Wouldn`t you have to do that with excel formulas, moving the cells values around.

Or, you could try using Flynet and crank the SQL youself to being the data back in that exact format.

ABILtd (BOB member since 2006-02-08)

system · August 28, 2008, 2:49pm

Josh,

QaaWs unfortunately outputs only as a vertical table, this is very frustrating when many of what customers want to see we need in crosstab format to display properly with Xcelsius. An easy solution is to use a combination of a crosstab and vlookup in excel.

Please see attached spreadsheet!

In excel map where your raw data is coming in, I’m assuming you will have three columns…Yr,Qtr,Sales. Then in the column left of Yr, concatenate Yr&Qtr. Now you will have a unique lookup value in your raw data.
Next create a crosstab and hardcode your Yr and Qtr values exactly how they will look in the output of your query…you can even code for extra years since vlookup will return only the correct values to your crosstab.
For the vlookup formula simply concatenate the first row(Yr) and first column(Qtr) of your crosstab for your lookup_value in the first cell, this will lookup that unique value that you make in the raw data table. Make sure to include the raw data concatenation column in your table_array.
CrossTabExample.xls (15.0 KB)

Jefftommy07 (BOB member since 2007-12-05)

system · August 29, 2008, 3:51am

That’s nice tip Jefftommy07.
Need to hear more tips & tricks from u

Thanks

siauan (BOB member since 2008-04-14)

system · August 31, 2008, 2:07pm

Awesome, thanks heaps Jefftommy07. That is exactly what I was looking for.

Thanks again!! =)

josh.fletcher (BOB member since 2007-01-08)

system · September 11, 2009, 6:59pm

Brilliant Jefftommy07 … You saved a lot of time and effort for me. Thanks.

ssreedev (BOB member since 2006-05-05)

system · December 6, 2009, 8:14am

You saved me a lot of headaches! Thanks guys!

aguirre (BOB member since 2004-10-15)

system · December 9, 2009, 12:11am

Yes a neat trick Jefftommy07. Thanks

katullus (BOB member since 2009-08-21)