topic Re: Set analysis in an Interval expression in QlikView

Set analysis in an Interval expression

nicalbert — Wed, 25 Nov 2020 16:16:04 GMT

Good morning,

I do have a QV app where I display the number of items for "yesterday", using the following expression that works perfectly:

=count({<CreationDate= {'>=$(=DayStart(today()-1))<$(=DayStart(today()))'}, Itemtype={'1'}>} [Item Number])

I the same app, I am displaying the average time required to create each item, using the following:

=Interval(avg(ProcessEnd-ProcessStart),'hh:mm:ss')

Now, I have the following request from my colleagues: Can we get a gauge that shows only the average processing time for each item made yesterday. My guess is that I somehow have to match both expressions... But I have no clue how to do it?

Can someone provide information on that?

Thanks!

Re: Set analysis in an Interval expression

sunny_talwar — Wed, 18 Apr 2018 14:57:15 GMT

May be you need to add the same set analysis to your avg expression

=Interval(Avg({<CreationDate= {'>=$(=DayStart(today()-1))<$(=DayStart(today()))'}, Itemtype={'1'}>} ProcessEnd-ProcessStart),'hh:mm:ss')

Re: Set analysis in an Interval expression

nicalbert — Wed, 18 Apr 2018 15:13:14 GMT

Thanks Sunny,

This expression works, but it returns the exact same value as the average one I use, as if the set analysis was not "working" (i.e. not affecting the data selection).

Nic

Re: Set analysis in an Interval expression

sunny_talwar — Wed, 18 Apr 2018 15:14:19 GMT

Are you sure that this same set analysis expression works within the other expression that you shared above?

Re: Set analysis in an Interval expression

nicalbert — Wed, 18 Apr 2018 15:17:25 GMT

You mean this one: =count({<CreationDate= {'>=$(=DayStart(today()-1))<$(=DayStart(today()))'}, Itemtype={'1'}>} [Item Number]) ? Yes, it does work and it provides accurate data on a daily basis.

Re: Set analysis in an Interval expression

sunny_talwar — Wed, 18 Apr 2018 15:18:21 GMT

Would it be possible for you to share a sample or sample data with the expected output from the sample you share?

Re: Set analysis in an Interval expression

nicalbert — Wed, 18 Apr 2018 16:04:22 GMT

Ok,

I just made the example you've requested using the exact same data as my original app and your expression, but this time it works and shows data correctly.

I don't get it!

Re: Set analysis in an Interval expression

sunny_talwar — Wed, 18 Apr 2018 16:10:31 GMT

Creation date is a timestamp field? Are you sure your first expression worked? Because if the set analysis worked for the 1st one... it should work for the second time... if it didn't work for the first time... it could be because of the creation date set analysis?

Re: Set analysis in an Interval expression

nicalbert — Wed, 18 Apr 2018 16:22:32 GMT

Got it to work...

Replaced the gauge from the initial app by the one I created for you and BAM! it worked!

You rock, thanks for your time!