topic Re: Need help on app developpement in QlikView

Need help on app developpement

guillaume_gorli — Wed, 22 Jul 2015 13:46:54 GMT

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 :

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

Re: Need help on app developpement

qumniusomnius — Wed, 22 Jul 2015 14:13:31 GMT

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

Re: Need help on app developpement

guillaume_gorli — Tue, 28 Jul 2015 09:36:25 GMT

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

I will have a look at your qvw by today.

Re: Need help on app developpement

guillaume_gorli — Wed, 29 Jul 2015 07:53:22 GMT

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

Re: Need help on app developpement

qumniusomnius — Wed, 29 Jul 2015 08:45:11 GMT

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.

Re: Need help on app developpement

guillaume_gorli — Wed, 29 Jul 2015 09:48:44 GMT

Ok that does make sence.

Thanks Borja