topic Re: How do I calculate a column - another column? in QlikView

How do I calculate a column - another column?

Mon, 11 Nov 2013 14:13:00 GMT

I have attached a little table as an example of what I am trying to achieve.

In the example, I have example people. They all need to be trained 6 hours, so 360 minutes. The 'Training Actual' column will show how much training they have done. The 'Training Target' will show the target they are to reach. If the 'Training Actual' is over 360, I want it to be counted as 360.

On Qlikview, if I do:

IF('Training Actual' > 'Training Target', 0, 'Training Target' - 'Training Actual') ... it will give 61 because it takes the totals.

It doesn't calculate that for every individual person. If it did, it would give me the results in 'Training Required'. That is what I am trying to get but it's proving to be really hard in Qlikview right now.

If anyone knows how to do this, please help.

Thanks in advance.

Regards,

Bruno

Re: How do I calculate a column - another column?

marcus_malinow — Mon, 11 Nov 2013 14:19:32 GMT

Hi Bruno,

a couple of things

I think the expression you want is:

IF([Training Actual] > [Training Target], 0, [Training Target] - [Training Actual])

In Total Mode you should also specify 'Sum of Rows' rather than the default 'Expression Total'

Marcus

Re: How do I calculate a column - another column?

Gysbert_Wassenaar — Mon, 11 Nov 2013 14:41:39 GMT

Try: rangesum([Training Target],-rangemin(360,[Training Actual]))

Re: How do I calculate a column - another column?

Mon, 11 Nov 2013 15:55:11 GMT

sorry, that was just an example so you guys understand what I was trying to do, that actual formula I have is more complex and correct but just giving me the totals instead of calculating the individual people.

The picture I attached was just from Excel, it was just to try to explain.. sorry for any confusion..

Bruno

Re: How do I calculate a column - another column?

marcus_malinow — Mon, 11 Nov 2013 16:06:30 GMT

Bruno,

In Total Mode you should also specify 'Sum of Rows' rather than the default 'Expression Total'

Marcus