Set analysis Date Range

[tyagishaila]

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

[Anil_Babu_Samineni]

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

[sunny_talwar · Accepted solution]

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

[Anil_Babu_Samineni]

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.

[sunny_talwar]

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

[sunny_talwar]

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

    1-Oct-16, 1-Oct-16

    2-Oct-16, 2-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

];

[Anil_Babu_Samineni]

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

AutoNumberHash128(Date1, Date2) as ID

Does it make sense?

[sunny_talwar]

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

[tyagishaila]

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

[sunny_talwar]

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)

Close

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