Time Change Formulas

[#1]

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?

[pover]

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.

[#3]

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%

[pover]

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.

[#5]

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.

[pover]

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