topic Re: Re: Row wise calculation in QlikView

Row wise calculation

Fri, 06 Sep 2013 07:22:12 GMT

Hi all,

I want to calculate the average of data based on time range. Like suppose i have 3 values each for 3 days,then i want to calculate the average of values based on a particular day.

Data is already loaded in qlikview through excel.

Please help me with this.

Thanks

Re: Row wise calculation

tresB — Fri, 06 Sep 2013 07:33:10 GMT

Is this what you want?

PFA

Re: Row wise calculation

Fri, 06 Sep 2013 07:46:50 GMT

hi tresesco

There is no attachment

Re: Re: Row wise calculation

tresB — Fri, 06 Sep 2013 07:52:38 GMT

It is there.

Still one more i am oploading, if it comes visible to you.

Re: Row wise calculation

Fri, 06 Sep 2013 08:28:49 GMT

No attachment. Can you please send it again?

Re: Re: Row wise calculation

tresB — Fri, 06 Sep 2013 08:40:31 GMT

Load this data,

Load * inline [
Date, Sales
06-09-2013, 100
06-09-2013, 200
06-09-2013, 300
06-09-2013, 400
07-09-2013, 300
07-09-2013, 400
07-09-2013, 500
];

Then Create a Straight table with Date as dimension, expression Avg(Sales). Check the result and let know.

Re: Re: Row wise calculation

Fri, 06 Sep 2013 08:51:36 GMT

considering your example, I have already loaded both fields Date and Sales.

Now I want to avg out the values based on dates and which should be independent of selection.

So if I am using chart, how I am supposed to write expression ?

ex.select avg(Sales) from ..... where date="06-09-2013 something similar to this.

Re: Re: Row wise calculation

tresB — Fri, 06 Sep 2013 08:57:00 GMT

This?

Avg ({<Date={'06-09-2013'}>} Sales)

Re: Re: Row wise calculation

Fri, 06 Sep 2013 09:18:29 GMT

PFA.

In this I have already loaded fileds 'Time_Val' & 'Efficiency' from my database.

Now I want another listbox which will have average values of efficiency based on date.

Re: Row wise calculation

tresB — Fri, 06 Sep 2013 09:31:47 GMT

In the script something like:

Load Avg(Efficiency) as AvgEff

From <> Group By Date;

Or list box expression:

Aggr(Avg(Efficiency), Date)

Re: Row wise calculation

Fri, 06 Sep 2013 09:47:20 GMT

tried with list box but its not working

Re: Row wise calculation

tresB — Fri, 06 Sep 2013 09:55:22 GMT

It should. Please share a small sample app. If you are worried about the data, you can always scramble(Settings->Document Properties->Scramble tab) the data and share a small sample (Select one year or such important dimension which substantially reduces data scope->File->Reduce Data-> Keep Possible Values, now your file is a relatively smaller one with dummy data, probably you can share).

Re: Row wise calculation

Sun, 08 Sep 2013 15:29:27 GMT

Hello Shweta,

Can you try this way:

Test:

LOAD * INLINE [

Time_Val, Efc

5/15/2013 12:00:00 AM, 10

5/15/2013 12:15:00 AM, 20

5/15/2013 12:30:00 AM, 40

5/15/2013 12:45:00 AM, 30

5/16/2013 12:00:00 AM, 10

5/16/2013 12:15:00 AM, 20

5/16/2013 12:30:00 AM, 40

5/16/2013 12:45:00 AM, 50

];

Test1:

LOAD

Timestamp#(Time_Val,'DD/MM/YYYY hh:mm:ss TT') as Time_Val1, Efc

Resident Test;

drop table Test;

And then use a straight table with

=date(floor(timestamp#(Time_Val1,'MM/DD/YYYY hh:mm:ss TT')),'MM/DD/YYYY') as Dimension

and

=Avg(Efc) as expression.

Hope this helps.

Thanks,

ASINGH