topic need help in pick function in QlikView

need help in pick function

geam1988 — Fri, 18 May 2018 12:53:17 GMT

Hallo,

I have used following function to get difference between two months but unable to get result in Qlikview.

If(month = 'jan',0, data - Pick (data, -1)) as Difference

Can anybody help me with it.

Best regard

Kazi

Month	Sales	Difference
Jan	1000	0
Feb	1200	200
Mar	1250
Apr	1500

Re: need help in pick function

vishsaggi — Fri, 18 May 2018 16:43:41 GMT

Are you looking for this?

IF yes you can try like below:

SalesDiff:

LOAD *, IF(Month = 'Jan', 0, Sales - Previous(Sales)) AS Diff INLINE [

Month, Sales

Jan, 1000

Feb, 1200

Mar, 1250

Apr, 1500

];

Then use straight table and add

Dim: Month, Sales

Expr: Diff

Then uncheck suppress 0 values in Presentation tab.

Re: need help in pick function

MarcoWedel — Fri, 18 May 2018 21:00:18 GMT

Did you mistake Pick for Peek?

Pick

Peek

regards

Marco

Re: need help in pick function

geam1988 — Tue, 22 May 2018 07:32:35 GMT

Hallo Vishwarath Nagaraju, thank you for your reply. if there is any change in sales value, do i always have to change the sales value in schript ? is it possible to do smothing in expressions ?

Re: need help in pick function

geam1988 — Tue, 22 May 2018 07:35:27 GMT

Hallo Marco Wedel,

i am talking about pick . i am looking for solution. if the result came from another function i have no problem to use that.

Re: need help in pick function

vishsaggi — Tue, 22 May 2018 16:00:18 GMT

I don't find any reason that you have to change your sales. Am i missing something? Please elaborate the issue. Do the script solution not work for you?

Re: need help in pick function

MarcoWedel — Tue, 22 May 2018 18:35:00 GMT

Hi,

one possible solution using Peek instead of Pick might be:

table1:
LOAD Month(Date#(Month,'MMM')) as Month,
    Sales,
    Alt(Sales-Peek(Sales),0) as Difference
FROM [https://community.qlik.com/thread/301877] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

regards

Marco

Re: need help in pick function

MarcoWedel — Tue, 22 May 2018 18:48:06 GMT

one example using a chart expression rather than generating the Difference field in the script:

hope this helps

regards

Marco