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.
paulyeo11
Valued Contributor II

Jan 2016 close stock amt is eqal to Feb 2016 Open stock , Feb 2016 close stock amt is eqal to Mar 2016 Open stock. I have Jan 2016 Close stock amt , how to make Feb 2016 display as open stock ?

Hi All

I have Table 1 exprssion from Mr sunny , display close stock amount in Table 1 :-

(

money(

(RangeSum(Above(RangeSum

(Above(

Sum

(

{

<

year, month, sTOCK={sTOCK}

>

}

[Amount]*1)/Rate, 0, RowNo()))),

-

Sum(

{

<

sTOCK={sTOCK}>

}

TOTAL Aggr(Sum(

{

<

year, month, sTOCK={sTOCK}

>

}

[Amount]*1)/Rate, YearMonth

))))

, $(vMoneyFormatK))

)

Table 1

yearMonth CLOSED STOCK AMT

2016 JULY 1,521,449

2016 JUNE 1,558,736

2016 MAY 1,505,156

2016 APR 1,422,434

2016 MAR 1,452,717

2016 FEB 1,413,910

2016 JAN 1,206,673

May i know how to get the open stock amount as below :-

yearMonth OPEN STOCK AMT

2016 JULY 1,558,736

2016 JUNE 1,505,156

2016 MAY 1,422,434

2016 APR 1,452,717

2016 MAR 1,413,910

2016 FEB 1,206,673

Remark :-

Jan 2016 close stock amt is eqal to Feb 2016 Open stock

Feb 2016 close stock amt is eqal to Mar 2016 Open stock

Hope some one can help me modify the expression , So tht i can have additional column display Open stock amount at Table 1.

Paul

1 Solution

Accepted Solutions
vishsaggi
Esteemed Contributor III

Re: Jan 2016 close stock amt is eqal to Feb 2016 Open stock , Feb 2016 close stock amt is eqal to Mar 2016 Open stock. I have Jan 2016 Close stock amt , how to make Feb 2016 display as open stock ?

write a second expression as:

Below(

[COLSE STOCK AMT]

,1)

6 Replies
paulyeo11
Valued Contributor II

Re: Jan 2016 close stock amt is eqal to Feb 2016 Open stock , Feb 2016 close stock amt is eqal to Mar 2016 Open stock. I have Jan 2016 Close stock amt , how to make Feb 2016 display as open stock ?

my qvw

vishsaggi
Esteemed Contributor III

Re: Jan 2016 close stock amt is eqal to Feb 2016 Open stock , Feb 2016 close stock amt is eqal to Mar 2016 Open stock. I have Jan 2016 Close stock amt , how to make Feb 2016 display as open stock ?

write a second expression as:

Below(

[COLSE STOCK AMT]

,1)

vishsaggi
Esteemed Contributor III

Re: Jan 2016 close stock amt is eqal to Feb 2016 Open stock , Feb 2016 close stock amt is eqal to Mar 2016 Open stock. I have Jan 2016 Close stock amt , how to make Feb 2016 display as open stock ?

Might not be big deal but if you are pushing this to PROD some time in future please correct the label for expression as

[CLOSE STOCK AMT]. Just the spelling.

With the above expression in my previous msg one of the last field for Open stock for 96 Jan will be Null. If you want the same stock amt just use Alt(Below([CLOSE STOCK AMT],1), [CLOSE STOCK AMT]).

Thanks,
V.

paulyeo11
Valued Contributor II

Re: Jan 2016 close stock amt is eqal to Feb 2016 Open stock , Feb 2016 close stock amt is eqal to Mar 2016 Open stock. I have Jan 2016 Close stock amt , how to make Feb 2016 display as open stock ?

Hi Vish

Thank you very much , your expression work fine.

Paul

paulyeo11
Valued Contributor II

Re: Jan 2016 close stock amt is eqal to Feb 2016 Open stock , Feb 2016 close stock amt is eqal to Mar 2016 Open stock. I have Jan 2016 Close stock amt , how to make Feb 2016 display as open stock ?

Hi Vish

Thank you very much i notice that you are very detail. programmer need to be very detail. Me not programmer very careless. 

I can accept 1996 the Open closed null.

I just like to test your expression below :-

Alt(Below([CLOSE STOCK AMT],1), [CLOSE STOCK AMT])


But it return null.


Paul

vishsaggi
Esteemed Contributor III

Re: Jan 2016 close stock amt is eqal to Feb 2016 Open stock , Feb 2016 close stock amt is eqal to Mar 2016 Open stock. I have Jan 2016 Close stock amt , how to make Feb 2016 display as open stock ?

No problem. Glad it worked. Its all live and learn, I am not a deep programmer though . Just keen at looking things and learnt a lot from all the Experts thought processes. :-)

Community Browser