Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Creator

## Help with dates??

Hi All,

I need help with calculations based on dates.

I have following data

ID     Status     RenewDT     CancelDT

1       A              2016/03

2       A              2016/06

3       A              2017/01

4       A               2017/01

5       A               2017/02

6       C                                  2017/01

7       C                                  2017/01

8       C                                  2017/02

I want to produce below table. I am unable to produce the below table with the above data.

Period            Total ID          Renewed           Cancel

2017/01                 3                   2                          2

2017/02                  2                   1                          1

2017/03                  0                   0                          0

2017/04                 0                     0                         0

2017/05                  0                   0                          0

2017/06                  0                   0                          0

2017/07                 0                   0                          0

2017/08                 0                   0                          0

2017/09                 0                   0                          0

2017/10                 0                   0                          0

2017/11                  0                   0                          0

2017/12                  0                   0                          0

Thanks

1 Solution

Accepted Solutions
Anonymous
Not applicable

Assuming the TotalID for 2017/01 is 4. Attached is your output.

7 Replies
Master II

What is the logic to create output table?

Creator III

Hi Nic,

As per my understand i solved it some what let me know if any queries u have and provide the sample data too

find the attachment

Master II

Try this code:

Temp:

[

ID,Status,RenewDT,CancelDT

1,A,2016/03,

2,A,2016/06,

3,A,2017/01,

4,A,2017/01,

5,A,2017/02,

6,C, ,2017/01

7,C, ,2017/01

8,C, ,2017/02

];

NoConcatenate

Temp2:

ID,Status,RenewDT as Period,1 as DTStatus

Resident Temp

where len(RenewDT)>0;

ID,Status,CancelDT as Period,2 as DTStatus

Resident Temp

where len(CancelDT)>0;

Drop Table Temp;

stage1:

,Period

Resident Temp2

where Status = 'A'

Group by Period;

Left Join

,Period

Resident Temp2

where Status = 'C'

Group by Period;

Left Join

,Period

Resident Temp2

Group by Period;

DROP Table Temp2;

Creator
Author

Thanks a lot,

But I want the output table like below

Period            Total ID          Renewed           Cancel

2017/01                 3                   2                          2

2017/02                  2                   1                          1

2017/03                  0                   0                          0

2017/04                 0                     0                         0

2017/05                  0                   0                          0

2017/06                  0                   0                          0

2017/07                 0                   0                          0

2017/08                 0                   0                          0

2017/09                 0                   0                          0

2017/10                 0                   0                          0

2017/11                  0                   0                          0

2017/12                  0                   0                          0

Creator
Author

Thanks a lot,

But I want the output table like below

Period            Total ID          Renewed           Cancel

2017/01                 3                   2                          2

2017/02                  2                   1                          1

2017/03                  0                   0                          0

2017/04                 0                     0                         0

2017/05                  0                   0                          0

2017/06                  0                   0                          0

2017/07                 0                   0                          0

2017/08                 0                   0                          0

2017/09                 0                   0                          0

2017/10                 0                   0                          0

2017/11                  0                   0                          0

2017/12                  0                   0                          0

Anonymous
Not applicable

What is the logic for Total ID??

Anonymous
Not applicable

Assuming the TotalID for 2017/01 is 4. Attached is your output.

Community Browser