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: 
guillaume_gorli
Creator II
Creator II

Need help on app developpement

Dear All,

I a facing several issues trying to build a specific app :

The database i am using is a part numbers inventory list showing inventory details such as, stock qty, past consumption details, future usage, cost, etc...

Capture.JPG

i need to classify part numbers based on their consumtion :

Category 1 should be all the part numbers for which y1 = 0 AND future_usage = 0. Thus, as per my test database, part number C should be classified this way for a stock value of 36€.

Capture.JPG

For this one, no particular problem to find out

Capture.JPG

Then it gets more complicated :

Category 2 should full fill several condirions :


Condition 1 : Variation between y2 and y1 is down more than 50 %. For this i created a value in the script named [Variation conso] that works fine  :

Capture.JPG

in my test database, part number A, C and E should be classified this way.


First issue is that i can't make this in a chart I was thinking to use set analysis (Sum ({$ <[Variation Conso] <= {"-0.5"}>}(tot_cost))) but for some reason it does not work.Below is the output of such a formulas :


Capture.JPG

Although what i am looking for is :

Capture.JPG

Condition 2 : A part number classified in category 1 can't be classified in category 2. in the example part number C is already classified in category 1 so it has not to be taken into account.

So the chart should looks like :

Capture.JPG

Then it gets tricky :

What has to be taken into account as a stock value is not the qty in stock by the unit cost but the qty in stock minus the future_usage multiplied by the unit cost. The thing is now i have negative qties that have to be replaced by zero (probably using a if condition ?)

My chart output is :

Capture.JPG

although it should be :

Capture.JPG

Bottom line, final chart should then be :

Capture.JPG

or :

Capture.JPG

So to sum up : 3 issues :

  • How to take into account in a chart only part numbers for which variable [Variation Conso] is smaller or equal to 0,5 using set analysis ?
  • How not to take into account part numbers in category 2 that are classified under category 1 ?
  • How to replace all negative values of variable stock value v2 by zero ?

For those who want to help, i attached qvw as well as database under excel format.

Thanks in advance

Guillaume

1 Solution

Accepted Solutions
qumniusomnius
Contributor III
Contributor III

the first expression you have problem with: Sum ({$ <[Variation Conso] <= {"-0.5"}>}(tot_cost)))

should be Sum ({$ <[Variation Conso] = {"<-0.5"}>}(tot_cost)))

to get rid of the rows you have to add:

if([Stock Value v1],  expression )

to all other expressions

for example: if([Stock Value v1], Num ([Variation Conso]))

to exclude values in first table simply substract them in the expression:

=(Sum ({ <[Variation Conso] = {"<-0,5"}>}(tot_cost))) -(Sum ({$ <[y1] = {0},[future_usage]= {0}>}(tot_cost)))

hope this helps, see the attachment

View solution in original post

5 Replies
qumniusomnius
Contributor III
Contributor III

the first expression you have problem with: Sum ({$ <[Variation Conso] <= {"-0.5"}>}(tot_cost)))

should be Sum ({$ <[Variation Conso] = {"<-0.5"}>}(tot_cost)))

to get rid of the rows you have to add:

if([Stock Value v1],  expression )

to all other expressions

for example: if([Stock Value v1], Num ([Variation Conso]))

to exclude values in first table simply substract them in the expression:

=(Sum ({ <[Variation Conso] = {"<-0,5"}>}(tot_cost))) -(Sum ({$ <[y1] = {0},[future_usage]= {0}>}(tot_cost)))

hope this helps, see the attachment

guillaume_gorli
Creator II
Creator II
Author

Sorry for the late reply Borja, i was off for a few days.

I will have a look at your qvw by today.

guillaume_gorli
Creator II
Creator II
Author

Borja,

Thankfs for the help. One question for you : what's the use of ticking the criteria "Activate condition" (see print screen below). I guess it is links with the expression if([Stock Value v1],  expression ) although It seems to have no impact on the charts wether i tick it or not?

Capture.JPG

Thanks

Guillaume

qumniusomnius
Contributor III
Contributor III

I think I was trying to use it to filter out lines that didnt have expression [Stock Value v1],

but it didnt work so I did it in all expressions with if([Stock Value v1],  expression ), and under presentation ticked suppress zero values.

guillaume_gorli
Creator II
Creator II
Author

Ok that does make sence.

Thanks Borja