Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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€.
For this one, no particular problem to find out
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 :
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 :
Although what i am looking for is :
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 :
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 :
although it should be :
Bottom line, final chart should then be :
or :
So to sum up : 3 issues :
For those who want to help, i attached qvw as well as database under excel format.
Thanks in advance
Guillaume
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
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
Sorry for the late reply Borja, i was off for a few days.
I will have a look at your qvw by today.
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?
Thanks
Guillaume
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.
Ok that does make sence.
Thanks Borja