7 Replies Latest reply: Aug 5, 2014 8:18 PM by Marco Wedel

# Calculation

Hello All,

I need a annual calculation, the following was done in excel is it possible in straight or pivot table in qlik ?

If so  in the annual column below each row is achieved by using this formula  FOR C5 ROW = ((B5-B4)/(A5-A4))*365

 A B C 1 Date Reading Annual 2 05/04/12 1,403,817 3 06/13/12 388,888 0 4 07/11/12 1,458,250 0 5 08/21/12 1,487,134 257,809 6 09/23/12 1,505,500 200,370 7 10/15/12 1,525,153 332,586 8 11/16/12 1,553,282 320,117 9 12/19/12 1,577,608 269,649 10 01/17/13 1,597,829 254,955
• ###### Re: Calculation

You could do something like

• ###### Re: Calculation

Hello Robert,

Thanks for the reply but the expression you gave me is giving different annuals from excel and what if readings are '0'

• ###### Re: Calculation

Do you have a sample qvw you could share?

• ###### Re: Re: Calculation

Hey Robert,

Hear is the sample QVW, I am not even able to populate any data in straight table

and I am sending you sample excel sheet I am looking for with calculations

• ###### Re: Calculation

Is the annual metric you're using supposed to end up being the change in readings over the change in the number of days?  What are the units you're working with?

• ###### Re: Re: Calculation

Hey Robert,

I am trying to calculate annual trends depending on the difference between readings and difference between dates formula or definition given by user

• ###### Re: Calculation

Hi,

one solution might be:

```tabInput:
(ooxml, embedded labels, table is Sheet1);

```

the reason for the slightly different results between your excel calculation and previously suggested QV solutions might be that your original post just included dates while your excel source contains timestamps with different times than 00:00:00.

hope this helps

regards

Marco