variable as dimension in chart

[gerhardl]

Hi,

I hope I can explain this properly.

I have a straight table chart, which must report on 3 things:

1. Number of Accounts Opened
2. Number of Accounts Closed
3. Number of Accounts Written Off

These are the expressions I use to show the numbers for the current month:

1. count({<[Account open month]={$(vCurrentMonth)},[Account open year]={$(vCurrentYear)}>}[Account no])
2. count({<[Month of status change]={$(vCurrentMonth)},[Year of status change]={$(vCurrentYear)},[Account status code]={'C'}>}[Account no])
3. count({<[Month of status change]={$(vCurrentMonth)},[Year of status change]={$(vCurrentYear)},[Account status code]={'Z'}>}[Account no])

Please see Image1 - this will then show me a table showing the book growth for September 2013.

What I want is to show the book growth PER MONTH, for the current year (or for the last 6 months is also okay).

So I want to use a variable as a dimension in the chart, and the expressions must all reference that. So that the chart eventually looks like Image 2. Of course the figures in Image 2 are totally wrong because I am using [Account open month] as a dimension.

Please let me know if you need more info. Note that I am using Personal Edition so cannot open your examples.

Thanks,

Gerhard

[tresesco]

Have you tried like this?

Pivot table, first dimension Account Status and second as Month. Drag the Month dimension on top

[gerhardl]

That won't work. The month used in Expression 1 is a different field than those used in Expressions 2 and 3. Please check the expressions.

I also can't use Status as a dimension - this is not the point of the chart...

[tresesco]

Please share your sample data in excel mentioning the desired output there.

[simondachstr]

Have you considering working with ValueList()?

[gerhardl]

Please see attached.

Hope it makes sense now...

[gerhardl]

I am not familiar with that but will look into it now...

[gerhardl]

Okay I think this might be the right track.

I now have a calculated dimension that looks like this:

=valuelist(vCurrentMonthMin6,vCurrentMonthMin5,vCurrentMonthMin4,vCurrentMonthMin3,vCurrentMonthMin2,vCurrentMonthMin1,vCurrentMonth)

The variables are equal to Sep, Aug, Jul, etc.

So now my chart looks like image 3 (attached).

but what do I do with my expressions - currently they look like this:

count({<[Account open month]={$(vCurrentMonth)},[Account open year]={$(vCurrentYear)}>}[A+ Status (A/L/C)])

In stead of using the vCurrentMonth variable in the expression, I want it to use the month of the column (the dimension):

count({<[Account open month]={$(the header of this column)},[Account open year]={$(vCurrentYear)}>}[A+ Status (A/L/C)])

[tresesco]

I guess this could be handled better with a GENERIC load. Have to work on it.

[gerhardl]

I appreciate all help - but please note that I do not want to follow an approach that changes my Load. This example is very basic, but will in fact be used in a pretty complex module, so I want to do this in the chart itself. This is not worth changing the load.

Just don't want you spending time on something that I cannot use..