show currently opened records for last 4 months

[#1]

Hi,

I have data in this format:

Problem ticket	Open Date	Close Date	Status
1	1/14/2015		Open
2	2/13/2016		Open
3	5/19/2016		Open
4	3/29/2017	5/22/2017	Close
5	6/8/2017		Open

I want to display currently opened records month wise in a bar chart in such a way that it only displays data of last 4 months including current month.

So the output should be like:

Month	Problem ticket
Apr	4
May	3
Jun	4
Jul	4

thanks in advance

[mangalsk]

You just need to create flag as if (status='Open','Open','Close')  as flag_status and in expression can write like

count({<flag_status={'Open'}>}distinct Problem_ticket)

[pathiqvd]

Hi,

   Try like this,

count({<status={"Open"},Opendate={">=$(=monthstart(addmonths(max(opendate),-4)))<=$(=max(opendate))"}>}fieldname)

Regards,

[venkat_dabbakut]

Hi ,

try this

count({<status={"Open"},Opendate={">=$(=monthstart(addmonths(max(opendate),-4)))<=$(=max(opendate))"}>}fieldname)

[PrashantSangle]

create Master Calendar which will contain all month

then using interval match() with master calendar replicate the ticket is open for the respected month.

Regards,

Prashant Sangle

[neha_shirsath]

Hi Sahil,

You can also create bucket as per days you filter out like -

difference between open date and close date and create bucket for the same in script

if([Open Date]-[Close Date]>=0 and [Open Date]-[Close Date]<=120,'0-120',

    if([Open Date]- [Close Date]>120 ,'>120'))))  as test

then

add in expression in chart -

Count({<flag_status={'Open'}, test={'0-120'}>}distinct Problem_ticket)