topic Re: Re: Re: Re: Query Please help in QlikView

Query Please help

Thu, 06 Nov 2014 06:43:56 GMT

Hi All,

I want to calculate like below

Year Month Average Salary Previous Salary + Average Salary

2010 Jan 15000 15000

2010 Feb 20000 35000

2010 March 16000 36000

like this

I reached till average salary ......but what function i should have to used for Previous + average ....I am calculating this in chart Expression I used Last() function as well .....but did not work..

Help in this regards

Sarfaraz Sheikh

Re: Query Please help

ychaitanya — Thu, 06 Nov 2014 07:01:10 GMT

Use

Load Year,month,Average_Salary,RangeSum(previous(Avergae_Salary),Average Salary) as Tota_Sal

Resident Table_Name

Group By Year,Month,Average_Salary.

Thanks

Chaitanya

Re: Query Please help

Thu, 06 Nov 2014 07:17:57 GMT

Please use the expression sum(AverageSal)

and Enable the below option

Accumulation: Accumulate 2 steps back

Re: Query Please help

its_anandrjs — Thu, 06 Nov 2014 07:19:57 GMT

Hi,

Or in load script try some thing this ways

LOAD Year, Month, [Average Salary];

LOAD * INLINE [

Year, Month, Average Salary

2010, Jan, 15000

2010, Feb, 20000

2010, March, 16000

2010,Apr,15000

];

NoConcatenate

New:

LOAD Year, Month,[Average Salary],

if(Year = previous(Year),Previous([Average Salary]) + [Average Salary], [Average Salary]) as Total

Resident A;

DROP Table A;

And final op

Regards

Anand

Re: Query Please help

Thu, 06 Nov 2014 09:47:06 GMT

Hi vasu,

I tried with the Accumulate ....but not working getting 0 in every row ...

Sarfaraz

Re: Query Please help

Thu, 06 Nov 2014 09:54:51 GMT

i thinki u can use in chart level like

total =avg salary+above(sum(avgsalary),1)

Re: Query Please help

Thu, 06 Nov 2014 11:10:38 GMT

Hi Anand,

You Manually Entered the Average salary in your case ........But in my case i am finding it using Average function ...So i tried with average function in my script but it is not working .......

request you to tell me another logic ........

Is it possible to use any relevent fuction in Chart Expression for achieve the same .......

Sarfaraz

Re: Re: Query Please help

anbu1984 — Thu, 06 Nov 2014 11:21:34 GMT

Check this app

Re: Re: Re: Query Please help

Fri, 07 Nov 2014 07:29:07 GMT

Hi anbu,

Thanks for your help ...but not getting as expected output ...please check the below attached Qvw file..

Sarfaraz

Re: Re: Re: Re: Query Please help

anbu1984 — Fri, 07 Nov 2014 07:55:34 GMT

Check this app

Re: Query Please help

jagan — Fri, 07 Nov 2014 08:01:47 GMT

Hi,

Use this expression

=Avg(SALARY) + Alt(Above( TOTAL Avg(SALARY),),0)

Regards,

jagan.

Re: Query Please help

Fri, 07 Nov 2014 10:59:08 GMT

Thanks a lot mr anbu.....i got the solution as expected ...........can you please explain about the alt function ...what it does actually ...i am not familier with this function ....

Sarfaraz

Re: Query Please help

Fri, 07 Nov 2014 11:00:27 GMT

Thanks to all ..........I got the solution ...but what is alt functin ....what are its functionality request with all you guys to explain me in brief....

Sarfaraz

Re: Query Please help

Fri, 07 Nov 2014 11:04:33 GMT

Hi sarfaraz,

could you please post the solution as I was also trying the same but could not achieve it. I am using personal edition.

Thanks

Re: Query Please help

anbu1984 — Fri, 07 Nov 2014 11:07:42 GMT

From QV help

The alt function returns the first of the parameters that has a valid number representation. If no such match is found, the last parameter will be returned. Any number of parameters can be used.

Alt(Above( TOTAL Avg(SALARY),),0) -- In case of first row, Above() returns null. So Alt(Above(),0) returns last parameter 0.

Re: Query Please help

Fri, 07 Nov 2014 11:09:12 GMT

alt(sales,0) it ignore null values

alt(date(date#(datefield,mm/dd/yyyy)),date(date#(datefield,mm-dd-yyyy))

it returns the correct date among this

Re: Query Please help

Fri, 07 Nov 2014 11:09:49 GMT

extract from help:

The alt function returns the first of the parameters that has a valid number representation. If no such match is

found, the last parameter will be returned. Any number of parameters can be used.

Example:

alt( date#( dat , 'YYYY/MM/DD' ),

date#( dat , 'MM/DD/YYYY' ),

date#( dat , 'MM/DD/YY' ),

'No valid date' )

Re: Query Please help

Fri, 07 Nov 2014 11:27:39 GMT

Hi Pooja,

Please check the above attched QVW file given by anbu cheliyan.........That will clear your doubts...

Sarfaraz

Re: Query Please help

Fri, 07 Nov 2014 11:36:25 GMT

I can not open qvw created by other user.

could you please attach it as image so that i can also understand how this requirement achieved.

or if it achieved by any set expression could you please copy that set expression in this thread.

Thanks

Re: Re: Query Please help

Fri, 07 Nov 2014 11:43:12 GMT

Dear Pooja,

please have the look on attached image.......

Sarfaraz