topic Re: Formula in QlikView

Formula

Tue, 29 May 2012 21:08:04 GMT

We have multiple records per day of specific brands and departments. Each record is averaged for the day and listed in a straight table and a line chart.

The records are tests with the count of "Good" and "Error" tests, aggregated per hour.

Results is 1-(error/success)

goal is always 95%

Over under is (results - goal)

I have attached a spreadsheet with what I am looking for, which is:

The combined department 1/2/3 has a goal but is dependent on the results of the other ones.

For the combined department 1/2/3, the goal needs to be the product of the other three departments' results and 95%

Example, for May 1 for Brand 1, the Combined department goal would be: 99% x 98% x 96% x 95% (Dept 1 x Dept 2 x Dept 3 x 95%) = 87%. This will alleviate the burden on the combined department to meet a goal when the contributing departments can't make their results.

I hope this makes sense!!

How do I incorporate the results of a formula of one row into another row, and specifically this formula.

Thanks for any input you can provide.

Re: Formula

Jason_Michaelides — Tue, 29 May 2012 23:45:58 GMT

Thanks mainly to some marvellous wizadry from Stefan (swuehl) in my post here, I have been able to put this in a pivot table for you using Dimensionality() and the aforementioned genius's code.

Hope it helps,

Jason

Re: Formula

Jason_Michaelides — Thu, 31 May 2012 14:57:56 GMT

Hi Susan,

If you have further questions on this please post them here so all may benefit!

Cheers,

Jason

Re: Formula

Thu, 31 May 2012 15:13:55 GMT

OK, and wow - what a great solution.

How does it work?

How do I get the 4th line (the combined department) to show?

How do I use this value in other objects, like line charts, showing the trend of the errors vs. goal over time?

By all 4 products?

I will have more questions.......

Re: Formula

Jason_Michaelides — Fri, 01 Jun 2012 09:32:43 GMT

Hi Susan,

How does it work? - err...not sure actually!! The use of exp() and log() is a bit beyond my mathematical understanding. That solution came from Stefan.

How do I get the 4th line (the combined department) to show? - it is showing in the pivot table (the Total line)

How do I use this value in other objects, like line charts, showing the trend of the errors vs. goal over time? Just use

Min(Aggr(exp(rangesum(above(log(1-Sum(errors)/Sum(successes)),0,RowNo())))*.95,date,brand,Department)) in whatever chart you like (see attached)

By all 4 products? If product is another dimension in you data model just add it in to the charts.

Hope this helps,

Jason

PS - please mark the correct answer for the benefit of all.

Re: Formula

Jason_Michaelides — Thu, 07 Jun 2012 07:03:32 GMT

Hi Susan,

Please mark a correct answer so this thread appears solved.

Thanks,

Jason