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

Formular sorting one number

hey @ all,

i am having a problem with a diagram and the values which shoud be shorted for this diagramm.

The Problem:

I had to make some diagrams from some orderings but the order is the last two numbers from a value in this number.

For example:

Ordernumber1: 100458500030

Ordernumber2: 100898500080

My testing idea was to that there is a condition using the formula like =if(OperationId=(*30),OperationId) but the *30 isn't working.

For this diagram i want to sort after 30 and 80.

All with 30 at the end in one diagramm and all 80 in the the other one.

I didn't find any way to make this working.

And my desire is that i can generate diagrams after every, i will say status(30,80), for this at runtime.

15 Replies
Not applicable
Author

Hey Ruben,

Thx for your answer, i am a newby and can't follow you.

I think there could be a way over one step more:

I made a listbox, where die OrderIDs where sorted like ( 20, 50, 80)

and now i want to read the data form this listbox like

=sum(if(Right(OperationId, 2)='THE_FRIST_NUMBER_ID_IN_THE_LISTBOX',opm.act.yield.primary))

And for this i can make one formula for every diagramm like the followed ones:

Diagramm 1:

=sum(if(Right(OperationId, 2)='THE_FRIST_NUMBER_ID_IN_THE_LISTBOX',opm.act.yield.primary))

Diagramm 2:

=sum(if(Right(OperationId, 2)='THE_SECOND_NUMBER_ID_IN_THE_LISTBOX',opm.act.yield.primary))

For this die diagramms and the listbox is very dynamic for every costcenter because of there are other operationIDs in the listbox for every costcenter.

Thx for your help!

Not applicable
Author

Any idea?

rubenmarin

Hi Tim, sorry about the delay but I'm busy and I can't elaborate any sample.

Just some tips in case those helps you:

- Using "concat(distinct FieldNameInListBox, ';')" or "getfieldselections(FieldNameInListBox, ';')" you can get all the values in the list box separated by a semicolon (you can change the separator). The getfieldselections option only retrieves all selected values, the concat option returns all posible values.

- Based on this string you can use Subfield() to get the value in a determined position, i.e., using Subfield(Concat(distinct FieldNameInListBox, ';'), ';', 2), you'll get the second value of the possible values in the listbox.

So you can try with:

Diagram 1: =sum(if(Right(OperationId, 2)=Subfield(Concat(distinct FieldNameInListBox, ';'), ';', 1),opm.act.yield.primary))

Diagram 2: =sum(if(Right(OperationId, 2)=Subfield(Concat(distinct FieldNameInListBox, ';'), ';', 2),opm.act.yield.primary))

Not applicable
Author

Hello Ruben,

Thx for your Help, i think it's the one i want to have but,

i can't go on because i don't know how qlikview knows which listbox/element it should use:

In the picture you can see the listbox is got after filling in the formula and the frist number in the orange circle (placeholders 1-2-3) i want so see in the chart above. If there is a 50 in the frist row then the chart shows there numbers.

For this your answer should be the right one but how can i use (distinct FieldNameInListBox, to get these numbers?

Qlikview tells me there is an error in the formula.

I think it's a my mistake because of didn't know much about qlikview works.

Example.png

(The window at the right are the properties form the listbox.

Thanks for your help again.

rubenmarin

Hi Tim, as said above (check the "post) if you create a field in the script it will be easier, as you can use that field name.

Now you have an expression in the list box, not a named field.

Mark_Little
Luminary
Luminary

HI,

Why don't you create this in script so you can easily access in the set analysis.

Right(OperationId, 2)  as OperationSort,


Then in your set analysis you can just do thing like


Sum({<OperationSort={30}>Value}

Sum({<OperationSort={80}>Value}

Sum({<OperationSort,={30,80}>Value}


Mark