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: 
Anonymous
Not applicable

Calculationg fill rate

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

fr.png

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

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!

View solution in original post

10 Replies
jerem1234
Specialist II
Specialist II

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!

Anonymous
Not applicable
Author

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....

jerem1234
Specialist II
Specialist II

Can't open file, getting access denied.

Anonymous
Not applicable
Author

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

jerem1234
Specialist II
Specialist II

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?

Anonymous
Not applicable
Author

Table removed...

jerem1234
Specialist II
Specialist II

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!

Anonymous
Not applicable
Author


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!

jerem1234
Specialist II
Specialist II

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!