Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Show the current vs current-40 no values

Hi All,

            It seems small requirement, not striking properly the syntax. Please find the attached file, in that same format, we have year,seq no and respective value, apart from that we need one more expression called previous seq no value (which is seqno -40) we have to do and show the value.

Please let me know for further queries on it.Output file is also in that as value1.

Regards,

Ravi.

1 Solution

Accepted Solutions
RedSky001
Partner - Creator III
Partner - Creator III

See the attached file, note I formatted the date in the load script.

(Above & Before, can both be used on a pivot table depends which axis your dimension is on, see both examples attached)

View solution in original post

10 Replies
RedSky001
Partner - Creator III
Partner - Creator III

Are you saying for each Seqno you want to create a new column value for "previous seq no value".

This will be ontained from finding the "Value" where the Seqno is equal to the current Seqno - 40?

 

Ie.

Where the current Seqno is 41.  The "Value" os Seqno 1 (41 - 40) is -2392.  This value will go in the  "previous seq no value" column for the current Seqno 41.

Is that correct, if it is then you may want to change the colum headeer from "previous seq no value" as it's not.

Also what happens where there is no previous seqno, should they just be zero?

 

Mark

Not applicable
Author

Yes..Exactly..

RedSky001
Partner - Creator III
Partner - Creator III

Does this help?

X:

LOAD YEARR,

     Month,

     Seqno,

     Value 

FROM

(biff, embedded labels, table is Sheet2$)

;

load *

,Lookup('Value','Seqno',Seqno-40,'X') as PrevValue

Resident X;

drop table X;

Not applicable
Author

Good Solution but we need to do this one in Chart expression level, if you could share then it will be great.

Regards,

Ravi.

RedSky001
Partner - Creator III
Partner - Creator III

Assuming the chart is always ordered by seqNo you could use:

Above(Total sum(Value),40)

Not applicable
Author

Hi,

      Thanks for your quick answer, but i have pivot table, if it is there some other solution around on it.

Note: With straight table it does work. i am happy with that.

Regards,

Ravi.

RedSky001
Partner - Creator III
Partner - Creator III

Should just be a case of using before(Total sum(Value),40) might need some tweaking...

Not applicable
Author

Yes, i agree with you, Please take some time on it and let me know. Not an issue, but it is some what triky one.

Regards,

Ravi.

RedSky001
Partner - Creator III
Partner - Creator III

See the attached file, note I formatted the date in the load script.

(Above & Before, can both be used on a pivot table depends which axis your dimension is on, see both examples attached)