Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
Hi,
Try like this,
count({<status={"Open"},Opendate={">=$(=monthstart(addmonths(max(opendate),-4)))<=$(=max(opendate))"}>}fieldname)
Regards,
Hi ,
try this
count({<status={"Open"},Opendate={">=$(=monthstart(addmonths(max(opendate),-4)))<=$(=max(opendate))"}>}fieldname)
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
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)