Help with Dimensions & Expresions on a Pivot Table

[#1]

Hello!

First, i must say i'm a newbie with QV. Sorry if i can't explain myself in an appropiate way.

In addition, i'm not completely good with English. I'm from Argentina. What a challenge for you all!

I'll try to explain what i'm trying to do:

I have a table with orders.. it means:

a customer id, an order_date, an order_id, a product_id, quantity, etc.. for a couple of years.

In addition, i have a calendar vinculated..

In the calendar, i can select just one year, and just one month. One period.

First, i wanna clasificate customers in 2 groups:

old customers: those who has orders in the last six months (depending on the month/year selected)

"new" customers: those who hasnt orders in the last six months (depending on the month/year selected)

Then, what i'm trying to do is a pivot table with, for each day in the month, how many diferent customers have placed the first order in the month (in other words, how many customers has been "activated" in that day of the month. Just the first order of a customer in the month counts as an activation), separated by Old and New customers.

Something like that.

Type	01-Mar-2011	02-Mar-2011	03-Mar-2011	04-Mar-2011	05-Mar-2011
Old Customers	103	201	156	187	133
New Customers	15	13	17	12	18

I made an expression to make the first classification (old vs new, depending on mon/year selected), but i can't use it as a calculated dimension (it gives me an error). I don't know if make an expression is the best approach for that... And later i have to solve not only if is old or new regarding the previous 6 months of the period selected, i have also to clasificate if there are activations or not for that day in the current period.

Could you please send some suggestions to help me out?

Samples, how-to's, etc.. any comment will be appreciated!

Thanks in advance.

[#2]

can you send me any sample data file i'll do it

thanks

Meher

[johnw]

A typical error for calculated dimensions is if you use an aggregation function like sum() without putting it in an aggr().  So it's possible you could just fix the calculated dimension in some way.

As a different way to approach the problem, your calculated dimension could be:

=valuelist('Old Customers','New Customers')

Then in the expression, do something like this:

if(valuelist('Old Customers','New Customers')='Old Customers', expression for old customers, expression for new customers)

[#4]

Meher: Thank you! , i'll try first John's suggestion. If i'm in troubles to get results, i'll upload a file..

John: Thanks for the tip! I didn't now such function valuelist, looks like a better way. I'll try and let you know. Thanks again!