Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
praveensprabhu
Contributor
Contributor

Need help to find first negative occurring value in the column

Hello Everyone,

I am looking for some help to resolve my Qlikview query.

I have to find the first occurrence of negative value from a series of values derived from an expression.

I am posting my sample data below and the result I am looking for which I need a way to achieve the same.

I have tried Firstsortedvalue() function, but not getting the desired result.

Solution I tried : 

praveensprabhu_0-1664187668438.png

When I used FirstSortedValue() function, I am getting the value of lowest -ve number (i.e., -5 and corresponding FMG28). But I should get first occurring -ve value i.e., FMG24 corresponding to -1, shown in below image.

desired solution:

praveensprabhu_1-1664187866179.png

I will be adding this as a calculated dimension in my Pivot chart as I will be pivoting unit and demand column in my dashboard and I will be representing this result as a value corresponding to each component_number .

How to achieve this ? Kindly help me.

 

Labels (1)
1 Reply
deepanshuSh
Creator III
Creator III

Hi Praveen, since you want the first occurrence of the negative value in the specific column, to do that you need to group your data by creating a composite key using primary key and the target column (Unit &'|'&Demand)  and then order it based on the value, there by giving a series wise order of the demand column or  you can use the date column if possible. Untill you use a second column to mark the first or last arrival of the negative value you cant determine its first occurrence. 

Trial and error is the key to get unexpected results.