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

[sunny_talwar]

Happy Dussehra Shaila!

Try this:

Results are not matching 100% and I had to change the dates so that it works with Today().

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];

LOAD * INLINE [

    Created Date, Close Date

    3-Oct-16, 3-Oct-16

    4-Oct-16, 4-Oct-16

    5-Oct-16,

    6-Oct-16,

    7-Oct-16, 9-Oct-16

    8-Oct-16,

    9-Oct-16, 9-Oct-16

    10-Oct-16

];

Straight table dimension

=If([Created Date] > Today() - 3 and [Created Date] <= Today(), Dual('1 to 2 days', 1),

If([Created Date] > Today() - 7 and [Created Date] <= Today() - 3, Dual('3 to 6 days',2), Dual('More than 7 days',3)))

Straight table expression

=Count(DISTINCT If(Date >= [Created Date] and Len(Trim([Close Date])) = 0, ID))

I think the mis-match is because of 8-Oct date (from the new data). Is it 1 to 2 days or is it 3 to 6 days since Today() is Oct 11th. If you always want to do a comparison to Today - 1, then you can try this dimension for your straight tab

=If([Created Date] > Today() - 4 and [Created Date] <= Today() - 1, Dual('1 to 2 days', 1),

If([Created Date] > Today() - 8 and [Created Date] <= Today() - 4, Dual('3 to 6 days',2), Dual('More than 7 days',3)))

I hope this will help you. I would also request you to consider marking the helpful response which you think might be helpful for others in the future. You don't have to overdo this but you can mark as many helpful responses as you would like.

Qlik Community Tip: Marking Replies as Correct or Helpful‌

Best,

Sunny

[tyagishaila]

Thanks Sunny,

Your solutions are really very helpful for me.

Actually I need to show bucketing expressions month wise.(in my original app)

e.g for Aug comparison starts with  31-aug-16.

For open cases 1 to 3 days means-

those Open cases which are lying in between 29-aug to 31-aug

Same for sep month 28-sep to 30-sep

and for Oct month 109-oct to 11-oct.

I will try to do it by your method.

Thanks!

[sunny_talwar]

Yes give it a shot and see what you get. If you still have issues, then you can always come back to the community

[tyagishaila]

Nahi hua by this method

[sunny_talwar]

Would you be able to share a sample where we can see some more realistic data with the more realistic output ? Tab shayad ho jaye

[tyagishaila]

Hi

Good Morning!

FollowUp CS_Dashboard is main app. (Do it by yourself bcoz you/no-one can't understand my report.)

and required result is highlighted in excel.

Thanks

[sunny_talwar]

What is Date3 in your expression? Did you rename that to be called something else?

[tyagishaila]

Date3 is nothing,

I created so many useless fields to get Open cases. ye bhi aisi hi koi hai..

attached file does not have extra fields, you can use it.

[sunny_talwar]

One more question , does the sum of these ranges need to add up to the total number of open cases for each of the rows? the reason i ask this is because this is not currently happening in your Excel file and I wonder why it doesn't

[tyagishaila]

yes it is same

sum of bucketing must be equal to total no of open cases.

But in excel total open cases are for both Impacting and Non-Impacting but bucketing is separating

Impacting & Non-Impacting.

Currently Non-Impacting is only for >=7, If we show for <7 than sum will be same as Total Open Cases.

Thanks

Shaila