topic Calculating Order Share by State by Month and Year in QlikView

Calculating Order Share by State by Month and Year

Sun, 18 Apr 2010 18:30:34 GMT

I'm trying to build a chart where I have 3 dimensions: year, month and Revenue (by Order) where the chart shows % of revenue contributed by each state during each month. I know I need to do a Sum(Revenue)/Sum({???} Revenue), but I can't figure out the set variables to use.

Calculating Order Share by State by Month and Year

Oleg_Troyansky — Sun, 18 Apr 2010 19:13:15 GMT

You should have 3 dimensions: Year, Month and State,

and your expression should be:

Sum(Revenue)/sum(total <Year, Month> Revenue)

Calculating Order Share by State by Month and Year

Mon, 19 Apr 2010 18:18:36 GMT

Thanks Oleg, this gets close, but not quite there. I want to show multiple months on the graph and with this formula, I get the (revenue for each state), divided by (total revenue summed across all months that show on the graph). In other words, each column (month) sums to less than 1 and I want each Month to sum to 1.

Calculating Order Share by State by Month and Year

Oleg_Troyansky — Mon, 19 Apr 2010 21:02:41 GMT

If you used the "total" correctly, you should be dividing by the sum for that Month only, and the total for the month should always be 1:

total <Year, Month>

The key is to specify your Year and Month dimensions inside the triangular brackets < >

If you post an example, I'll try to show you ...

Calculating Order Share by State by Month and Year

Mon, 19 Apr 2010 21:46:04 GMT

Thanks Oleg. Your solution was right on, I was accidentally mixing columns between the Dimensions and Expression.