topic Calculating a value on a level below the dimension. in QlikView

Calculating a value on a level below the dimension.

Fri, 06 Apr 2012 16:01:10 GMT

Hello,

I am creating a dashboard that projects revenue by sales team for this year based on the growth we saw last year. The complex part of what I am trying to do is calculate this projection on a level below team ( say product category) and then add up each category for each team to get the value . I do not want to use product category as a demension since there are too many and it's too much to look at. Is there a way to do this?

Any suggestions would be appreciated.

Thanks,

Jeff

Calculating a value on a level below the dimension.

Jason_Michaelides — Sat, 07 Apr 2012 06:53:31 GMT

You can maybe put Product Category inside a set analysis expression. What is your current expression and what the dimensions so far?

Calculating a value on a level below the dimension.

Mon, 09 Apr 2012 13:04:05 GMT

Hi Jason,

As of right now I only have sales team as a demension. As for expressions , I am using 3 similar expressions for revenue to calculate projected revenue.

1. Rev for this year as of today. Sum({$<RevYear={"2012"},Snapshotdate={"Today"}>}ActiveRev)

2. Rev last year (YTD) Sum({$<RevYear={"2011"},Snapshotdate={"Today-366"}>}ActiveRev)

3. Rev End Last Year Sum({$<RevYear={"2011"},Snapshotdate={"2011yrend"}>}ActiveRev)

Here is the projection expression

(3/2) *1

Is it an issue that I am already using set analysis in the expressions?

Thanks for the help!

Calculating a value on a level below the dimension.

swuehl — Mon, 09 Apr 2012 13:40:09 GMT

Jeff,

it sounds to me that you would need to use advanced aggregation, something like

=sum(aggr( YOUREXPRESSIONS, Team, [Product Category] ))

and replace YOUREXPRESSIONS with the formula you stated above.

Hope this helps,

Stefan