Cumulated percentages

[#1]

Hi QlikView Community,

again I am confronted with a problem i am not able to solve.

I built a table with our sales, the rows are the 12 months and the columns are Actuals, Budget, PreYear.

Additionally I added two columns to compare Actuals to Budget and to PreYear as percentages.

This table I have copied and chosen "total cumulation", so I have one table with the single values and one with the cumulated vales per month.

The point is that the cumulation of values in % doesnt work (just by choosing the option in the diagram menu), QV just adds the percentages, e.g. -> Actuals vs Preyear for Jannuary 175% and for February 109%, so he shows 284% in the cumulated percentage column for february (instead of comparing the absolute values of January and February to the absolute values of Jan and Feb of PreYear)

The code is the following:

=

sum(if(inYearToDate(InvoiceDate,MaxOrderDate,0) and CostumerType='Dritte' ,SalesAmount)) /

sum

( {$ < YearInvoice= {$(=year(MaxOrderDate))}, CostumerType={Dritte} > } BudgetAmount

)

I welcome any idea for getting this work.

Many thanks in advance,

Christoph Peukert

[Anonymous · Accepted solution]

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

Christoph,

The Full Accumulation check box does not work as you would wish for columns with percentages. It simply adds up the percentage which helps nobody. Instead of using the Full Accumulation check box, you could use a range sum expression to do your accumulations. And then use =COLUMN(1)/COLUMN(2) for example for your percentage.

I would create hidden expressions for your normal expressions (the set analysis expressions) and label them and then use the labels in RANGESUM expressions to create your accumulation columns. Finally use those accumulated columns and divide to come up with the percentages. Here is how I would show that accumulated expression for your Actuals:

rangesum(Sum(Actuals),above(Sum(Actuals),1,rowno()))

Hope this helps.

Aaron Couron

Data Solutions Group

[#3]

Thanks a lot for your input Aaron,

I just had a problem with the labels, i labeled the formula (which works perfectly in the table and shows single values of months) as Actuals but when i code the column as sum(Actuals) it only shows the total value of the year in each row so i couldnt use it for rangesum functions.

Instead I used your rangesum function for my cumulated absolute figures but with the whole formula for Actuals instead of the label. Then column(1) / column(2) and it works perfectly!

Thanks!

[#4]

I have the same problem (I need to do a YTD ratio - accumulation year to date by month of productive hours over accumulation year to date by month of patient days), but it is a bit more complicated by the fact I need to use a pivot table rather than a straight table, and as far as I am aware, there is no way to hide expressions in a pivot table... they don't want to see the accumulation expressions in the chart (they don't want to see the accumulation of productive hours and the accumulation of patient days - it is not useful information for this chart). Any ideas???