topic Set analysis Date Range in QlikView

Set analysis Date Range

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

Dear Community members,

I have these two fields

Created Date	Close Date
1-Oct-16	1-Oct-16
2-Oct-16	2-Oct-16
3-Oct-16
4-Oct-16
5-Oct-16	7-Oct-16
6-Oct-16
7-Oct-16	7-Oct-16
8-Oct-16

And expected result is

	Total	Open	Close
1-Oct-16	1	0	1
2-Oct-16	2	0	2
3-Oct-16	3	1	2
4-Oct-16	4	2	2
5-Oct-16	5	3	2
6-Oct-16	6	4	2
7-Oct-16	7	3	4
8-Oct-16	8	4	4

means for every date , date range start with 1-Oct-16.

Total: suppose we check for 6-Oct-16 than total created date must be the total of 1st to 6th.

Open

Re: Set analysis Date Range

Anil_Babu_Samineni — Sun, 09 Oct 2016 05:19:14 GMT

What are these open and close, I mean those are Day Numbers or what

Re: Set analysis Date Range

sunny_talwar — Sun, 09 Oct 2016 05:38:31 GMT

Try this may be:

Script:

Table:

LOAD *,

Date([Created Date] + IterNo() - 1) as Date

While [Created Date] + IterNo() - 1 <= [Temp Close Date];

LOAD RecNo() as ID,

[Created Date],

[Close Date],

If(Len(Trim([Close Date])) = 0, Today()-1, [Close Date]) as [Temp Close Date]

FROM

[https://community.qlik.com/thread/235734]

(html, codepage is 1252, embedded labels, table is @1);

Dimension

Date

Expressions

Total: =RangeSum(Above(Count(If(Date = [Created Date], ID)), 0, RowNo()))

Open: =Column(1) - Column(3)

Close: =RangeSum(Above(Count(If(Date >= [Created Date] and Date = [Close Date], ID)), 0, RowNo()))

Re: Set analysis Date Range

Anil_Babu_Samineni — Sun, 09 Oct 2016 05:47:03 GMT

Sunny, For TOTAL we can take simple Rowno() right, Why do we require to use Rangesum between those Field and Rowno(). Please share me future advantage to use this while you become free.

Re: Set analysis Date Range

sunny_talwar — Sun, 09 Oct 2016 05:48:47 GMT

Right now we only have one ID created each day. What if we have multiple IDs created each day or if have no ID created for a particular day? In those cases, using RowNo() won't work

Re: Set analysis Date Range

sunny_talwar — Sun, 09 Oct 2016 05:51:12 GMT

Here is an example

Table:

LOAD *,

Date([Created Date] + IterNo() - 1) as Date

While [Created Date] + IterNo() - 1 <= [Temp Close Date];

LOAD RecNo() as ID,

[Created Date],

[Close Date],

If(Len(Trim([Close Date])) = 0, Today()-1, [Close Date]) as [Temp Close Date];

LOAD * INLINE [

Created Date, Close Date

1-Oct-16, 1-Oct-16

2-Oct-16, 2-Oct-16

4-Oct-16,

5-Oct-16, 7-Oct-16

6-Oct-16,

7-Oct-16, 7-Oct-16

8-Oct-16

];

Re: Set analysis Date Range

Anil_Babu_Samineni — Sun, 09 Oct 2016 05:51:49 GMT

Nie Thought, Thanks. Why not to use use those two field as a key

AutoNumberHash128(Date1, Date2) as ID

Does it make sense?

Re: Set analysis Date Range

sunny_talwar — Sun, 09 Oct 2016 05:54:15 GMT

Well the OP won't need to create an ID field, this should already be there is his field. I created it in order to do the counts. So, I don't know how AutoNumberHas() work, but if it assigns a unique id to each row, then that might work as well . I know RecNo() does that, so used RecNo().

Best,

Sunny

Re: Set analysis Date Range

tyagishaila — Sun, 09 Oct 2016 10:32:18 GMT

Hi Sunny,

Thank-you so much for correct and immediate reply.

Please let me know How can I get Open value with rangesum() except Total-Close

Thanks

Re: Set analysis Date Range

sunny_talwar — Sun, 09 Oct 2016 12:46:18 GMT

Something like this can work for Open

=Count(If(Date >= [Created Date] and Date < If([Temp Close Date] = Today() - 1, [Temp Close Date] + 1, [Temp Close Date]), ID))

I also modified the Total and Close to work when you make selection in a specific day.

Total

=RangeSum(Above(Count({<Date>}If(Date = [Created Date], ID)), 0, RowNo())) * Avg(1)

=RangeSum(Above(Count({<Date>}If(Date >= [Created Date] and Date = [Close Date], ID)), 0, RowNo())) * Avg(1)

Re: Set analysis Date Range

tyagishaila — Sun, 09 Oct 2016 13:02:38 GMT

Thanks a ton.

Re: Set analysis Date Range

tyagishaila — Mon, 10 Oct 2016 11:36:31 GMT

Hi Sunny,

I have further requirements.

Noe I need to show all open cases in bucket.

Open cases for 1 to 3 days

Open cases for 4 to 7 days

Open cases for 8 to 16 days

Open cases for 16 to 30 days

Open cases for more than 30 days.

For example: for month Aug

Open cases for 1 to 3 days means cases open before 3 days.

i.e. open cases between date 29-aug-16 to 31-aug-16.

Thanks

Re: Set analysis Date Range

sunny_talwar — Mon, 10 Oct 2016 11:39:44 GMT

tyagishaila‌ -

I think its better you open a new thread for a new set of requirement as it would be difficult for others in the future to track down what this thread was all about. In addition, you will get more set of eyes by creating a new thread.

Best,

Sunny

Re: Set analysis Date Range

tyagishaila — Mon, 10 Oct 2016 11:40:51 GMT

Okay

Re: Set analysis Date Range

tyagishaila — Mon, 10 Oct 2016 15:51:29 GMT

Actually I am not able to make it a new thread,

Sorry I have no idea of it.

Please help me out to make a bucket of time period

to know that how many open cases are open in 1 to 3 days , in 4 to 7 days.... so on for each month.

Thanks

Re: Set analysis Date Range

Anil_Babu_Samineni — Mon, 10 Oct 2016 16:02:35 GMT

Usually, We can create buckets like this. Sunny, This is just guess correct me if i am wrong

Use this as a calculated dimension

If([Open case] >=Day([Open case],'1') <= Day([Open case],'3'), Dual('1 to 3 days',1),

If([Open case] >=Day([Open case],'4') <= Day([Open case],'7'), Dual('4 to 7 days',2),

If([Open case] >=Day([Open case],'8') <= Day([Open case],'16'), Dual('8 to 16 days',3),

If([Open case] >=Day([Open case],'17') < Day([Open case],'30'), Dual('17 to 30 days',4),

If([Open case] >=Day([Open case],'30'), Dual('More than 30 days',5))))))

Re: Set analysis Date Range

sunny_talwar — Mon, 10 Oct 2016 16:04:48 GMT

This is right Anil, but we need to use an expression instead of Open Cases because Open cases is a calculation here and not a field from the script. We will need to use Aggr() function to get the results.

Re: Set analysis Date Range

sunny_talwar — Mon, 10 Oct 2016 16:16:44 GMT

Anil -

I take my response back, you might be right after all. I am testing and will let you know soon.

Best,

Sunny

Re: Set analysis Date Range

sunny_talwar — Mon, 10 Oct 2016 16:35:14 GMT

From the sample provided, what is your expected result?

Re: Set analysis Date Range

tyagishaila — Tue, 11 Oct 2016 03:16:33 GMT

@Sunny

Happy Dussehra!

Suppose we are checking for 8-oct

Created Date	Close Date	OPen for 8-oct
1-oct-16	1-oct-16
2-oct-16	2-oct-16
3-oct-16		1
4-oct-16		1
5-oct-16	7-oct-16
6-oct-16		1
7-oct-16	7-oct-16
8-oct-16		1

Output must be

open for 1 to 2 days

open for 3 to 6 days

open for more than 7dys

8-0ct to 6-oct

5-oct to 2-oct

before 1-oct

Total

In above output table 2nd row is not a part of output , it is just to understand range of date for which open cases are calculating.

Thanks