topic Daily Average based on cumulative sales line chart in QlikView

Daily Average based on cumulative sales line chart

Thu, 12 Nov 2009 12:20:24 GMT

I am need to recreate a chart I have in Excel that is based on the daily averages per quarter.

On work day 1 we did $100 in sales so daily average is $100, on work day 2, we did another $50 in sales so now we hve $150 divided by two so daily avg is $75 (150/2), etc... the line chart should show $100 Day 1 $75 day two and so onh. How do I do this in the expressions. I currently have a full accumlation by day by quarter to show Year over Year and Quarter of Quarter but I wnat to change this to the daiy average line chart.

Daily Average based on cumulative sales line chart

Anonymous — Thu, 12 Nov 2009 14:13:47 GMT

Hi, I hope I got this correctly. Here's an example file on how to create this expression in a table. It should work in a line chart too for example but if you start putting the expression in a pivot table and doing a crosstable pivot it might break.

Daily Average based on cumulative sales line chart

Thu, 12 Nov 2009 15:23:18 GMT

you can also use "rangeavg(above(sum(s),0,rowno()))" in the expression where "s " is daily sales

Daily Average based on cumulative sales line chart

Anonymous — Thu, 12 Nov 2009 15:28:23 GMT

Of course! That's much better.

Daily Average based on cumulative sales line chart

Thu, 12 Nov 2009 22:52:32 GMT

Thank you both for the assistance. I am having issues getting it to work, but am sure I will understand. First I need to know what the functions do. "Above" is not in the help files. Can you tell me its purpose?

Daily Average based on cumulative sales line chart

johnw — Fri, 13 Nov 2009 01:34:29 GMT

It's in there, but it's hidden under "chart inter record functions" since above is such a common word.

above( [ total ] expression [ , offset [,n ]] )

Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table or, in the case of bitmap charts, in the chart's straight table equivalent (Actually all QlikView charts have a straight table equivalent with the exception of the pivot table which has a more complex structure.).

On the first row of a column segment a NULL value will be returned, as there is no row above this one.

If the chart is one-dimensional or if the expression is preceded by the total qualifier, the current column segment is always equal to the entire column.

If the table or table equivalent has multiple vertical dimensions, the current column segment will include only rows with the same values as the current row in all dimension columns except for the column showing the last dimension in the inter field sort order.

The inter field sort order for pivot tables is defined simply by the order of the dimensions from left to right. For other chart types this can be manipulated in the Chart Properties: Sort dialog.

Specifying an offset greater than 1 lets you move the evaluation of expression to rows further up the current row. A negative offset number will actually make the above function equivalent to a below function with the corresponding positive offset number. Specifying an offset of 0 will evaluate the expression on the current row. Recursive calls will return NULL.

By specifying a third parameter n greater than 1, the function will return not one but a range of n values, one for each of n table rows counting upwards from the original cell. In this form, the function can be used as an argument to any of the special range functions.

Examples:

sum( Sales ) / above( sum( Sales ))

above( sum( Sales ), 2 )

above( total sum( Sales ))

rangeavg (above(sum(x),1,3))

returns an average of the three results of the sum(x) function evaluated on the three rows immediately above the current row.

Daily Average based on cumulative sales line chart

Fri, 04 Dec 2009 01:28:34 GMT

Thank you. Now, I am almost there but still not working correctly.

It is dividing the sales by the day # now using this formula:

rangesum(above(sum( [Total Booked ($)] ), 0, [Work Day #])) /[Work Day #]

But, it is dividing that DAY's sales and not the accumulation thru that day. When I change to Full Accumulation, the line chart does not work.

What I need is for the chart to accumulate the days sales each day till end of quarter and on each day, divide the accumulated sales by that Day #. Then reset the accumulation for the next quarter and start over so that I have a line for each day.

The X axis of my line chart is the Work Day # and each line represnts a quarter so I can compare quarter over quarter how we are doing on daily average. Example Q1 we were averaging 20k on day # 3 and on Q2 we were averaging 30k on day #3

Daily Average based on cumulative sales line chart

Fri, 04 Dec 2009 01:30:14 GMT

when I change to full accumulation it does not reset for each new quarter, it just keeps accumulating the sales from the previous quarter.

Daily Average based on cumulative sales line chart

Mon, 11 Jan 2010 15:17:32 GMT

EDIT: Seems like I had bad data - with a different selection and w/o total line the below works for a line chart as well.

All,

I've tried the above and works well for using sum (straight table & line chart). What I try to achieve though is to calculate average prices ( Sum Revenue / Sum Quantity).

The following formula only works when I use a straight table though - a line chart shows the error message 'No Data to display'.

=(rangesum(above(Sum(REV_MEAS)/Sum(QTY), 0, rowno())) / rowno())

I have accumulation turned OFF... Any Ideas? Thanks for your help!

Lukas

Daily Average based on cumulative sales line chart

Mon, 26 Apr 2010 21:12:42 GMT

Did you ever figure out how to do this for a ine chart? I still have not been successful

Daily Average based on cumulative sales line chart

Tue, 27 Apr 2010 08:38:30 GMT

Hi there,

yes, with the help from John Witherspoon I managed to get it done, please have a look at this post:

http://community.qlik.com/forums/p/24315/92933.aspx#92933

Lukas

Daily Average based on cumulative sales line chart

christian77 — Tue, 27 Apr 2010 11:27:30 GMT

Eureka!!! try now with a pivot table. There is not row number when you expand only an item. Add a couple of columns on the right, and see their behaviour.

Dimensionality()& ' - ' & noofrows(Total) & ' - ' & rowno(Total)

Dimensionality()& ' - ' & noofrows() & ' - ' & rowno()

Thanks.