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: 
ptl14495
Contributor III
Contributor III

Previous Values

Hi,

 

I would like to have the difference value between the last value and the previous value.

Please note that the previous value, may be in the previous month, year, week or day.

For last value i have 

 FirstSortedValue(DISTINCT {<[REFERENCE] = {'A4BW''}>} [CURRENT COUNTER STATE],-[POSTING DATE])

 

thank you

6 Replies
tresesco
MVP
MVP

In firstSortedValue(), you can pass a rank argument. Hence use this rank parameter to get the second last value , like:

 

FirstSortedValue(DISTINCT {<[REFERENCE] = {'A4BW''}>} [CURRENT COUNTER STATE],-[POSTING DATE])
-
FirstSortedValue(DISTINCT {<[REFERENCE] = {'A4BW''}>} [CURRENT COUNTER STATE],-[POSTING DATE], 2)

 

ptl14495
Contributor III
Contributor III
Author

Hi Thanks for your response.

 

It gives back 0 😞

is there another way?

thank you

tresesco
MVP
MVP

There could be other ways. However, the one I suggested is one of the better ones. Hence, instead of quickly looking for alternative solution, I guess it would be better to look into the issue - why you got zero as output. Could you share a sample app to look into the issue?

ptl14495
Contributor III
Contributor III
Author

Hi, surte.

I have a table with facts in which, not all lines have Current Counter State Values. But all lines have dates on it.

i need to substract the last previous from last value to the last value.

Using the columns that i gave, do you think it's possible? If there is anything more i can give you, please let me know.

Once again, thank you so much!

 

tresesco
MVP
MVP

In other words, I guess you would need to consider those dates that has fact values, if so, you could try like:

 

FirstSortedValue(DISTINCT {<[REFERENCE] = {'A4BW'},[CURRENT COUNTER STATE]={"*"}>}
[CURRENT COUNTER STATE],-[POSTING DATE]) - FirstSortedValue(DISTINCT {<[REFERENCE] = {'A4BW'},[CURRENT COUNTER STATE]={"*"}>}
[CURRENT COUNTER STATE],-[POSTING DATE], 2)
ptl14495
Contributor III
Contributor III
Author

Hi,

Maybe i'm doing somthing wrong but it gives me null.

Capture.JPG

Is there anything more i can do?

 

thank you