topic Re: Issue need help in QlikView

Issue need help

Wed, 05 Nov 2014 07:09:12 GMT

Hi All,

I want to calculate the year wise average/sum of salary . I used year as dimension and

below expression in Expression:-

Avg(SALARY)

But..even after doing this i am getting same salary row wiese in every year row ...

Year Salary

2010 32000

2011 32000

please help me in this regards:---

Sarfaraz

Re: Issue need help

MK_QSL — Wed, 05 Nov 2014 07:11:31 GMT

Use

Avg(Aggr(SUM(Salary),Year))

Re: Issue need help

Wed, 05 Nov 2014 07:17:25 GMT

Hi manish,

still it is not working ......what is the use of Aggr function ...what it does

Sarfaraz

Re: Issue need help

MK_QSL — Wed, 05 Nov 2014 07:20:15 GMT

Provide me some more data or your sample apps

Re: Re: Issue need help

amit_saini — Wed, 05 Nov 2014 07:22:41 GMT

Check the attachment.

Thanks,
AS

Re: Re: Issue need help

Wed, 05 Nov 2014 07:27:55 GMT

Please check with the attachment !!!!!!!!

Sarfaraz

Re: Issue need help

MK_QSL — Wed, 05 Nov 2014 07:28:41 GMT

Load * Inline

[

Emplyee, Year, Salary

A, 2012, 100

B, 2012, 150

C, 2012, 200

A, 2013, 200

B, 2013, 250

C, 2013, 300

A, 2014, 200

B, 2014, 300

C, 2014, 400

];

Create a Straight Table

Dimension

Year

Expression

Avg(Salary)

Re: Issue need help

Wed, 05 Nov 2014 07:38:32 GMT

It is working with your example ..straight way you put year number there ......in my case i am taking Year from the field and not working in my case ....request you to please see the above attached file

Hope you will help me

Sarfaraz

Re: Issue need help

MK_QSL — Wed, 05 Nov 2014 07:44:26 GMT

Define your

Month and Year in script as below

This should work

Year(Date(Floor(Timestamp#(START_DATE)))) as Year

Month(Date(Floor(Timestamp#(START_DATE)))) as Month

Create a Straight Table

Use below as Calculated Dimension

=Aggr(Year(Date(Floor(Timestamp#(START_DATE)))),START_DATE)

Expression

AVG(SALARY)

Re: Issue need help

its_anandrjs — Wed, 05 Nov 2014 07:55:29 GMT

Hi,

In expression try this

Avg( TOTAL SALARY )

And see what you get also for 2010 and 2011 what is average value from your side.

Regards

Anand

Re: Issue need help

kogasawara — Wed, 05 Nov 2014 08:04:49 GMT

I cheacked your attached qvw file(setAnalysisTask.qvw).

In your qvw, two tables does not link.

Could you modify load script? like below

---------------------------------------------

Empp:

LOAD ADDRS,

BIRTH_DATE,

Year(START_DATE) as year,

D_NO,

FIRST_NAME,

LAST_NAME,

SALARY,

SEX,

SSN,

START_DATE,

SUPERSSN

FROM

---------------------------------------------

You need to get the year from Empp table.

Expression:

Avg(Aggr(SUM(SALARY),year))

Re: Issue need help

Wed, 05 Nov 2014 09:09:19 GMT

still i am getting same calculation ....

Year Average salary

2010 15800

2011 15800 like this...

sarfaraz

Re: Issue need help

Wed, 05 Nov 2014 09:36:25 GMT

Thanks Kogasawar and all ...i got the solution,

Sarfaraz

Re: Issue need help

MK_QSL — Wed, 05 Nov 2014 09:57:04 GMT

as per my solution below should work

Create a Straight Table

Use below as Calculated Dimension

=Aggr(Year(Date(Floor(Timestamp#(START_DATE)))),START_DATE)

Expression

AVG(SALARY)