Re: dates range in set analysis - Qlik Community

Anonymous · ‎2016-10-13

Hi

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

sunny_talwar · ‎2016-10-13

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?

Anonymous · ‎2016-10-14

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

sunny_talwar · ‎2016-10-15

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

Anonymous · ‎2016-10-15

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

sunny_talwar · ‎2016-10-16

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

Anonymous · ‎2016-10-16

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!

sunny_talwar · ‎2016-10-17

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

Anonymous · ‎2016-10-17

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

sunny_talwar · ‎2016-10-17

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