topic Time Change Formulas in QlikView

Time Change Formulas

Mon, 24 Jan 2011 18:32:46 GMT

I am able to use the formula below to calculate at %, but when I use this same formula on the "time chart" wizard it does not produce the same results.

Original:

(Sum ([Price Ext]) - Sum([Cost EXT]))/Sum ([Price Ext])

Time Chart Wizard

sum(if (InYear (Mdate, Today(), 0, 7),(Sum ([Price Ext]) - Sum([Cost EXT]))/Sum ([Price Ext])))

Any ideas?

Time Change Formulas

pover — Mon, 24 Jan 2011 18:42:36 GMT

You cannot nest the sum function in QlikView. Instead give the following expression a try:

sum(if (InYear (Mdate, Today(), 0, 7),([Price Ext] - [Cost EXT])/[Price Ext]))

Regards.

Time Change Formulas

Mon, 24 Jan 2011 20:13:11 GMT

Thanks Karl, but it is calculating each individual transaction and then summing them up at the end. This would not be an issue if I was not looking at %. Any other ideas?

Example

Calculation: 59,470.1%

Actual: 23.3%

Time Change Formulas

pover — Mon, 24 Jan 2011 20:22:44 GMT

Good point.

Try the following:

sum(if (InYear (Mdate, Today(), 0, 7),([Price Ext] - [Cost EXT]))
/
sum(if (InYear (Mdate, Today(), 0, 7),([Price Ext]))

Regards.

Time Change Formulas

Mon, 24 Jan 2011 23:19:12 GMT

I had to take it down one more level to make it work.

(sum(if (InYear (Mdate, Today(), 0, 7),[Price Ext])) -

sum(if (InYear (Mdate, Today(), 0, 7),[Cost EXT]))) /

sum(if (InYear (Mdate, Today(), 0, 7),[Price Ext]))

Thanks for the help Karl.

Time Change Formulas

pover — Tue, 25 Jan 2011 00:32:56 GMT

Please post the min and max expression of the gauge and the main expression to get a better idea or what is going on.