topic Re: dates range in set analysis in QlikView

dates range in set analysis

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

I have a pivot table with Date as my dimension.

I would like it to calculate based on each Date record in my dimension in my pivot table…

So for example rows in my pivot table look like:

Date Count

2016-08-01, 10 (count aggregated from 2016-7-25 to 2016-08-01)

2016-08-02, 8 (count aggregated from 2016-7-26 to 2016-08-02)

2016-08-03, 23 (count aggregated from 2016-7-27 to 2016-08-03)

I don't know how I should create my expression using set analysis. I tried different things like 'Count( {$< Date = {‘>=$(=max(Date)-7)<=$(=max(Date))’} , Date =>} Field1)'. But nothing worked.

Please help!

Thank yo

Re: dates range in set analysis

sunny_talwar — Thu, 13 Oct 2016 17:54:37 GMT

Set analysis won't work on row by row basis. You will need to use Aggr function here or may be you need to use RangeSum() with Above() function. Can you share some sample with expected output to help you better?

Re: dates range in set analysis

Anonymous — Sat, 15 Oct 2016 00:19:43 GMT

Thanks Sunny! Attached is my sample.

i'm creating an hourly census, but for each day i want it to only look at the range from 7 days before to current.

So for Aug 01 2016 I only want to calculate the count between 07-252016 to 08-01-2016 instead of all the dates in my data.

Please take a look and let me know.

thank you! !

Re: dates range in set analysis

sunny_talwar — Sat, 15 Oct 2016 22:18:30 GMT

Is this what you were looking to get?

So for each hour, the expression is summing the last seven day for that particular hour.

So 2016-08-01 Hour 0 will sum 7 days hour 0 numbers.

Expression:

=RangeSum(Above(TOTAL Count({<[Fiscal Month], [Fiscal Period], [Fiscal Quarter], [Fiscal Year], [Fiscal Year Month], [Fiscal Year Name], [Fiscal Year Quarter], [Month Name], [Month Year], [Month Year Previous]>} Patient), 0, 7)) * Avg(1)

You will have to ignore any date and time related field where you might be making selections. I have added few of them, but you may not need them all if you don't plan to make selection in them. An alternate approach would be to use The As-Of Table‌. This is fairly robust approach and I would recommend looking into this if you have no issue making changes in your script.

HTH

Best,

Sunny

Re: dates range in set analysis

Anonymous — Sun, 16 Oct 2016 00:24:23 GMT

Hi Sunny

Thanks a lot for looking into this and provide the solution.

But this is not what i was looking for. Let me provide some background of what i'm trying to achieve.

So this table is a patient census table, basically it's looking at every day at each hour, how many patients are there in

the ER room. So if a patient come in July 31 20:00 pm and leave Aug 01 02:00 am, he would be counted at below hours: July 31 20:00, July 31 21:00, July 31 22:00, July 31 23:00, Aug 01 00:00, Aug 01 01:00, Aug 01 02:00 those 7 buckets. I have already done that part. But because there is very occassionally some outlier data, for example, there could be a patient whose leaving date is more than 100 later than the coming date which is not possible. Those are wrong data. So what I want to do in set analysis is that at any point of time, a patient is only counted if the entering date is less than 7 days before.

Hope that explained the purpose of my question.

Thanks a lot for providing insight, it's very helpful. Especially i'm interested to know more about As-Of Table.

And if you have a solution to my elaborated problem, please let me know. Highly appreciate your help!

LeeAnn

Re: dates range in set analysis

sunny_talwar — Sun, 16 Oct 2016 22:11:58 GMT

Would you be able to share an example where you are seeing this? I am seeing a max duration on 40 hours within the sample. May be if I can see the issue, I might be able to provide a solution for it

Re: dates range in set analysis

Anonymous — Mon, 17 Oct 2016 00:00:39 GMT

thanks Sunny,

here is an example, so for Aug 01-2016 hour 0 the count is 46, the correct count should be 45.

Reason for that is there is an abnormal record with Patient Id : 2265638 which is registered more than a week ago prior to Aug 01 2016.

So in my example I want to ignore records like that but i don't know how i can specify that in set analysis.

I don't want my count take into account of those records that's registered in the ER more than 7 days before. Only the ones within 7 days period.

How can I do that?

Thanks!

Re: dates range in set analysis

sunny_talwar — Mon, 17 Oct 2016 17:58:49 GMT

May be try this:

=Count(Aggr(If(Count(Total <Patient> FullDate) <= 7, Patient), FullDate, Day, Hour, Patient))

This is completely ignoring any record which has entries for more than 7 days. In case you want to see them for only 7 days, then you can try this

=Count(Aggr(If(Min(Total <Patient> FullDate) + 7 > FullDate, Patient), FullDate, Day, Hour, Patient))

Re: dates range in set analysis

Anonymous — Mon, 17 Oct 2016 19:08:30 GMT

Hi Sunny,

Thanks a lot! this seems to work. Only that I need hours to be from 0 to 23 and I didn't load all hours in my previous example out of laziness (sorry about that). And I found this solution only works for hour 0.

How can i modify my expression so that it works for all 24 hours in a day?

Please see attached example after expanded the hours.

Thanks!!

LeeAnn

Re: dates range in set analysis

sunny_talwar — Mon, 17 Oct 2016 20:06:53 GMT

I think I updated the expression in my post, but forgot to fix it in the attached application. Try this where we have all dimensions from the chart in the Aggr() function here:

=Count(Aggr(If(Count(Total <Patient> FullDate) <= 7, Patient), FullDate, Day, Hour, Patient))

Re: dates range in set analysis

Anonymous — Tue, 18 Oct 2016 00:44:42 GMT

Thank you very much Sunny! can you please explain how it works?

In the example it worked fine for certain cell but not all. So in the example, i only inserted one error record where it's registered more than 7 days before. So the final result compare to previous count should be 1 count less in each hour for each day. Although i have seen many cells turn to be more counts less which shouldn't be the case.

But thank you so much for helping me along the way!

Re: dates range in set analysis

sunny_talwar — Tue, 18 Oct 2016 02:51:02 GMT

So, this isn't working perfectly in all cases? May be if you can point out those places where it doesn't work, we might be able to take a look at this.

Now that I am looking at this, the expression might need a DISTINCT in there to work

=Count(Aggr(If(Count(DISTINCT Total <Patient> FullDate) <= 7, Patient), FullDate, Day, Hour, Patient))

Can you check if that resolves all your issues. I can go over the details of what this expression is doing in my next response.

Best,

Sunny

Re: dates range in set analysis

Anonymous — Wed, 19 Oct 2016 00:02:11 GMT

Thanks Sunny, but that expression doesn't really work still. In my example, my data represents from time of patients going into emergency room(registration) to when they leave (disposition). So normally duration of patient in ER won't be longer than 7 days. So i added 1 abnorrnal record:

Patient Id : 2265638 where it's registered in 2015 and disposed in 2016, this is an error. My purpose is that i need to implement the logic so that this abnormal record is filtered out in chart, so the end result will be only 1 count less for every day every hour than if it were not filtered out.

Not sure if i'm explained what i was trying to do.

Please let me know.

Thanks!

LeeAnn

Re: dates range in set analysis

sunny_talwar — Wed, 19 Oct 2016 08:51:42 GMT

But isn't that what it did in the example where you added that patient id: 2265638? it counted one less patient? I think your last sample did not include 2265638, that is why we don't see any difference, but if it was there we would have seen one less patient

Re: dates range in set analysis

Anonymous — Wed, 19 Oct 2016 17:47:18 GMT

thanks Sunny!!... i added the patient 2265638 in to my sample... please see attached...

Re: dates range in set analysis

sunny_talwar — Wed, 19 Oct 2016 19:05:26 GMT

I made a slight change, may be this is what was causing the issue:

=Count(Aggr(If(Count({1}Total <Patient> FullDate) <= 7, Patient), FullDate, Day, Hour, Patient))

Re: dates range in set analysis

Anonymous — Wed, 19 Oct 2016 19:58:56 GMT

Hi Sunny,

I used your last expression, still didn't work.

I inserted one wrong record and my expected result is that the count in Before column is always 1 count more than the After Column.

Currently it's variant and i'm not sure what the expression is doing:

Thanks!!

LeeAnn

Re: dates range in set analysis

sunny_talwar — Wed, 19 Oct 2016 20:18:10 GMT

I guess I am not sure what exactly isn't working. If we can drill down to a very specific range of data and if you can explain what isn't matching, I might be able to help. I am sorry, I have not been very helpful thus far, but I am not sure what exactly is missing

Other alternative would be to wait for someone else to respond

Re: dates range in set analysis

Anonymous — Thu, 20 Oct 2016 17:55:29 GMT

Hi Sunny,

No worries, thanks very much for the help along the way!!

LeeAnn