Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
dseelam
Contributor

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

ABC
1DateReadingAnnual
205/04/121,403,817
306/13/12388,8880
407/11/121,458,2500
508/21/121,487,134257,809
609/23/121,505,500200,370
710/15/121,525,153332,586
811/16/121,553,282320,117
912/19/121,577,608269,649
1001/17/131,597,829254,955
1 Solution

Accepted Solutions

Re: Calculation

Hi,

one solution might be:

tabInput:

LOAD Date,

    Reading,

    (Reading-Previous(Reading))/(Date-Previous(Date))*365 as Annual

FROM [http://community.qlik.com/servlet/JiveServlet/download/582931-118876/ID%20277046.xlsx]

(ooxml, embedded labels, table is Sheet1);

QlikCommunity_Thread_128402_Pic1.JPG.jpg

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

7 Replies
Not applicable

Re: Calculation

You could do something like

=((Reading - above(reading))/(Date - above(Date))*365

dseelam
Contributor

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'

Not applicable

Re: Calculation

Do you have a sample qvw you could share?

dseelam
Contributor

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

Not applicable

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?

dseelam
Contributor

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:

LOAD Date,

    Reading,

    (Reading-Previous(Reading))/(Date-Previous(Date))*365 as Annual

FROM [http://community.qlik.com/servlet/JiveServlet/download/582931-118876/ID%20277046.xlsx]

(ooxml, embedded labels, table is Sheet1);

QlikCommunity_Thread_128402_Pic1.JPG.jpg

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

Community Browser