topic Re: Calcualted Dimension Question in QlikView

Calcualted Dimension Question

Wed, 05 Sep 2012 13:27:03 GMT

I'd like to create a straight table with an undefined dimension. They are actual 3 distinct (and sometimes overlapping) sets...

<Fiscal Year = {$(vMaxFY -1)}> ex. 2011

<[Fiscal Year]={$(=vMaxYear-1)}

,[Fiscal Period] = {"<=$(=vMaxFPinMaxFY)"} > ex. 2011 YTD

and

<Fiscal Year = {$(vMaxFY)}> ex. 2012 YTD

I have the following expressions.

Sum([Total Cost]) , Count([SSN]) , Avg([Total Cost])

I'm currently doing this with 9 text boxes, but I feel like a straight table would be a better solution.

Can someone provide some guidance???

Thanks and best regards,

Keith

Re: Calcualted Dimension Question

Anonymous — Wed, 05 Sep 2012 13:37:16 GMT

Maybe someone can correct me, but I am not sure this is possible in one straight table because your dimension rows (in this case your time periods) are overlapping. I would create 3 tables of one row each, run horizontally, still "dimensionless" with each of your three set types. I would do the 2011 one with a header row so you can label the expressions and then suppress the headers on the other two so I could align them up close to the first table so they appear as one table.

Re: Calcualted Dimension Question

Wed, 05 Sep 2012 13:43:07 GMT

I'm adding a picture of what is being displayed right now...

Re: Calcualted Dimension Question

Anonymous — Wed, 05 Sep 2012 14:02:02 GMT

What you have done by putting together all these text boxes is not bad and probably now that it is done, leave it alone??? But it will be a hassle when you want to move the beast around so it might be worth the work to get it into three tables that look as one rather than 9+ text boxes that look as one. Regardless, neither solution would give you one table. I drew this up in MS Excel to show you what I would do:

Table 1 (with Headers)

	Total Cost (Millions)	Volume of Encounters	Average Cost
2011	SUM({<Fiscal Year = {$(vMaxFY -1)}>} [Total Cost])	COUNT({<Fiscal Year = {$(vMaxFY -1)}>} SSN)	AVG({<Fiscal Year = {$(vMaxFY -1)}>} [Total Cost])

Table 2

2011 thru FP10

SUM({<[Fiscal Year]={$(=vMaxYear-1)}
,[Fiscal Period] = {"<=$(=vMaxFPinMaxFY)"} > } [Total Cost])

COUNT({<[Fiscal Year]={$(=vMaxYear-1)}
,[Fiscal Period] = {"<=$(=vMaxFPinMaxFY)"} > } SSN)

AVG({<[Fiscal Year]={$(=vMaxYear-1)}
,[Fiscal Period] = {"<=$(=vMaxFPinMaxFY)"} > } [Total Cost])

Table 3

2012 thru FP10

SUM({<Fiscal Year = {$(vMaxFY)}>} [Total Cost])

COUNT({<Fiscal Year = {$(vMaxFY)}>} SSN)

AVG({<Fiscal Year = {$(vMaxFY)}>} [Total Cost])

Hope this helps.