topic Statistics on calculated fields in QlikView

Statistics on calculated fields

Wed, 13 Apr 2011 15:40:12 GMT

Hi,

Attached I created a QV document, in wich I have created a small table.
Afterwards I created a statistics object, in which I added a new calculated column (via an expression).

The expression could be something like this:
[CODE]if(([End]-[Start])<='1','Yes','No')[/CODE]

Now, as a next step, I would like to know the percentage of 'Yes' (2) compared to the total of records (5) ==> So, the outcome should be 40%.

In the long end the goal is to see per [START] (timestamp) the percentage of 'Yes' as a chart.
So, in my example,
for [START]=1, I would want to see 33%
for [START]=2, I would want to see 100%
for [START]=3, I would want to see 0%

Thanks for your help!

Ps: I prefer not to do changes in the script. I would like to know if it is possible with either macro's, set analysis, ...

Regards,
Maarten

Statistics on calculated fields

vgutkovsky — Wed, 13 Apr 2011 16:59:21 GMT

Sure, this is possible with advanced aggregation (aggr). Your expression would be: sum(aggr(if(End-Start<=1,1),Id,Start))/sum(aggr(1,Id,Start,End))

Regards,

Statistics on calculated fields

Thu, 14 Apr 2011 15:11:56 GMT

Thanks Vlad, I'll give that one a try!

Statistics on calculated fields

Fri, 15 Apr 2011 07:20:55 GMT

I tried the solution, but I did not get the expected results. Besides that, the actual example is a bit more difficult (attached with this post).

I created a statistics object where I define if a target date is met by using a combination of expression...

Now I would like to know for that object how much (in percentage) of all reports meet the actual target date. It should be shown per week.

It might be quite simple, but I'm a bit overwhelmbed by the vast amount of code the calculated field contains now 🙂

Kind regards,

Maarten

Statistics on calculated fields

vgutkovsky — Fri, 15 Apr 2011 13:59:15 GMT

Maarten,

Please see the attached solution.

Cheers,

Statistics on calculated fields

Mon, 18 Apr 2011 07:59:07 GMT

Hi Vlad,

Exactly what I needed! Thanks a lot... I will take a closer look at your solution, asspecially where you configured the vOnTime variable 🙂

Thanks a ton!

Regards,

Maarten

Statistics on calculated fields

vgutkovsky — Mon, 18 Apr 2011 13:43:41 GMT

To view variables, go to Settings>Variable Overview. The vOnTime variable is your exact expression just pasted into a variable so we can use it elsewhere without having to retype the whole thing. The functionality is the same, it's just a matter of convenience.

Cheers,

Statistics on calculated fields

Tue, 26 Apr 2011 08:48:29 GMT

Dear Vlad,

Thanks for the information! ... (and sorry for my late reply, I was enjoying a week of holidays :-)).

Kind regards,

Maarten