topic Re: Calculationg fill rate in QlikView

Calculationg fill rate

Anonymous — Wed, 12 Feb 2014 16:37:11 GMT

Hi, I'm trying to calculate TAT 90% fill rate (among other expressions) I have a huge table loaded. The way to calculate it is...

1) Sort TAT by month (I have no way to sort it in load script due to table structure so it has to be done internally with an aggr exp or something)

2) Count number of rows per month

3) Calculate 90% out of step 2

4) Take TAT vaue for row calculated in step 3

Re: Calculationg fill rate

jerem1234 — Wed, 12 Feb 2014 17:09:53 GMT

Could you post a sample app?

If I were to guess, it might be along the lines like, if you have Month as dimension:

firstsortedvalue(TAT, Month, round(count(TAT)*.9))

but that might not work since you don't want to sort TAT within the Month (or create field for TAT order in script and sort by that)

or maybe:

subfield(concat(TAT, ','), ',', round(count(TAT)*.9))

But this would all depend on your data model and how things are sorted or needs to be sorted.

Hope this helps!

Re: Re: Calculationg fill rate

Anonymous — Wed, 12 Feb 2014 17:19:21 GMT

Hi Jerem1234,

I'm getting a memory exceeded error I'm attaching my workbook, the idea is adding a line in chart showing the fillrate, I've tested your expression in the table there....

Re: Calculationg fill rate

jerem1234 — Wed, 12 Feb 2014 17:26:09 GMT

Can't open file, getting access denied.

Re: Re: Calculationg fill rate

Anonymous — Wed, 12 Feb 2014 17:44:54 GMT

My bad... I forgot to remove section access script

Re: Calculationg fill rate

jerem1234 — Wed, 12 Feb 2014 18:05:32 GMT

Seems like the app keeps crashing on me, wondering if its because of that table you made. Could you re-upload without that table and if you could remove any unnecessary objects?

Re: Re: Calculationg fill rate

Anonymous — Wed, 12 Feb 2014 18:08:35 GMT

Table removed...

Re: Re: Re: Calculationg fill rate

jerem1234 — Wed, 12 Feb 2014 18:23:34 GMT

See if attached gives you the numbers you want. I used the formula:

subfield(concat(distinct TAT, ',', TAT), ',', round(count(distinct TAT)*.9))

Hope this helps!

Re: Re: Re: Calculationg fill rate

Anonymous — Wed, 12 Feb 2014 18:44:10 GMT

It doesn't, but playing a little with the expression I finally got what I was looking for, I just had to remove the distinct clauses:

subfield(concat(TAT, ',', TAT), ',', round(count(TAT)*0.9))

I honestly don't understand what's the formula doing, so if you could give me a quick explanation I'd appreciate it. Thank you very much!

Re: Calculationg fill rate

jerem1234 — Wed, 12 Feb 2014 18:59:19 GMT

First, I would think this would have been a prime example for firstsortedvalue(), but it seems like Qlikview doesn't like having a formula in its third parameter. Also if you weren't using distinct, then it wouldn't be able to return what you wanted.

To explain the equation:

concat(TAT, ',', TAT)

will concatenate all the TAT's(first parameter) into one big string with commas in between (second parameter) with the sort order of TAT (third parameter). So it sorts TAT by lowest to largest, then concatenates them with a delimiter of ','.

round(count(TAT)*0.9)

will get the count of TAT's for each respective month, multiply by .9, and then round.

subfield(concat(TAT, ',', TAT), ',', round(count(TAT)*0.9))

Subfield will take the concatenated string of all the TATs, then pick the respective subfield (between each delimiter of ',')

An example may help:

Subfield('Apple,Peach,Orange, Banana', ',', 2) will return Peach

Subfield('Apple,Peach,Orange, Banana', ',', 4) will return Banana

Hope this helps!

Re: Calculationg fill rate

Anonymous — Wed, 12 Feb 2014 19:13:58 GMT

Helps a lot! I wasn't getting what concat was doing, very usefull, thank you very much!