topic Re: Re: variable as dimension in chart in QlikView

variable as dimension in chart

gerhardl — Fri, 06 Sep 2013 11:24:38 GMT

Hi,

I hope I can explain this properly.

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

Number of Accounts Opened
Number of Accounts Closed
Number of Accounts Written Off

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

count({<[Account open month]={$(vCurrentMonth)},[Account open year]={$(vCurrentYear)}>}[Account no])
count({<[Month of status change]={$(vCurrentMonth)},[Year of status change]={$(vCurrentYear)},[Account status code]={'C'}>}[Account no])
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

Re: variable as dimension in chart

tresB — Fri, 06 Sep 2013 11:31:57 GMT

Have you tried like this?

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

Re: variable as dimension in chart

gerhardl — Fri, 06 Sep 2013 11:35:13 GMT

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...

Re: variable as dimension in chart

tresB — Fri, 06 Sep 2013 11:46:14 GMT

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

Re: variable as dimension in chart

simondachstr — Fri, 06 Sep 2013 12:23:24 GMT

Have you considering working with ValueList()?

Re: Re: variable as dimension in chart

gerhardl — Fri, 06 Sep 2013 12:27:27 GMT

Please see attached.

Hope it makes sense now...

Re: Re: variable as dimension in chart

gerhardl — Fri, 06 Sep 2013 12:27:53 GMT

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

Re: Re: variable as dimension in chart

gerhardl — Fri, 06 Sep 2013 12:38:58 GMT

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)])

Re: variable as dimension in chart

tresB — Fri, 06 Sep 2013 12:45:07 GMT

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

Re: Re: variable as dimension in chart

gerhardl — Fri, 06 Sep 2013 12:59:53 GMT

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..