Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have the following straight table with Tickets, Status, Create_Date, Year and Month:
TICKET | STATUS | CREATE_DATE | YEAR | MONTH |
---|---|---|---|---|
TCKT001 | Closed | 26/05/2016 | 2016 | May |
TCKT009 | Open | 17/06/2016 | 2016 | Jun |
TCKT010 | Open | 17/06/2016 | 2016 | Jun |
TCKT022 | Closed | 21/06/2016 | 2016 | Jun |
TCKT023 | Closed | 21/06/2016 | 2016 | Jun |
TCKT024 | Closed | 21/06/2016 | 2016 | Jun |
TCKT025 | Closed | 21/06/2016 | 2016 | Jun |
TCKT026 | Closed | 22/06/2016 | 2016 | Jun |
TCKT027 | Closed | 23/06/2016 | 2016 | Jun |
TCKT028 | Closed | 27/06/2016 | 2016 | Jun |
TCKT029 | Open | 28/06/2016 | 2016 | Jun |
TCKT030 | Closed | 28/06/2016 | 2016 | Jun |
TCKT031 | Closed | 28/06/2016 | 2016 | Jun |
TCKT032 | Closed | 28/06/2016 | 2016 | Jun |
TCKT033 | Closed | 29/06/2016 | 2016 | Jun |
TCKT034 | Closed | 29/06/2016 | 2016 | Jun |
TCKT035 | Open | 29/06/2016 | 2016 | Jun |
TCKT077 | Closed | 12/07/2016 | 2016 | Jul |
TCKT078 | Closed | 12/07/2016 | 2016 | Jul |
TCKT079 | Closed | 12/07/2016 | 2016 | Jul |
TCKT080 | Closed | 12/07/2016 | 2016 | Jul |
TCKT081 | Closed | 15/07/2016 | 2016 | Jul |
TCKT082 | Open | 15/07/2016 | 2016 | Jul |
TCKT083 | Open | 18/07/2016 | 2016 | Jul |
TCKT084 | Open | 18/07/2016 | 2016 | Jul |
TCKT085 | Closed | 18/07/2016 | 2016 | Jul |
TCKT086 | Closed | 18/07/2016 | 2016 | Jul |
TCKT087 | Closed | 18/07/2016 | 2016 | Jul |
TCKT088 | Closed | 19/07/2016 | 2016 | Jul |
TCKT089 | Closed | 22/07/2016 | 2016 | Jul |
TCKT090 | Closed | 22/07/2016 | 2016 | Jul |
TCKT091 | Closed | 22/07/2016 | 2016 | Jul |
TCKT092 | Closed | 29/07/2016 | 2016 | Jul |
TCKT093 | Closed | 03/08/2016 | 2016 | Aug |
TCKT094 | Closed | 03/08/2016 | 2016 | Aug |
TCKT095 | Closed | 03/08/2016 | 2016 | Aug |
TCKT096 | Closed | 03/08/2016 | 2016 | Aug |
TCKT097 | Closed | 03/08/2016 | 2016 | Aug |
TCKT098 | Closed | 05/08/2016 | 2016 | Aug |
TCKT099 | Closed | 05/08/2016 | 2016 | Aug |
TCKT100 | Closed | 05/08/2016 | 2016 | Aug |
TCKT101 | Open | 05/08/2016 | 2016 | Aug |
TCKT102 | Open | 05/08/2016 | 2016 | Aug |
TCKT103 | Closed | 05/08/2016 | 2016 | Aug |
TCKT104 | Closed | 05/08/2016 | 2016 | Aug |
TCKT105 | Closed | 06/08/2016 | 2016 | Aug |
TCKT106 | Closed | 09/08/2016 | 2016 | Aug |
TCKT137 | Closed | 29/08/2016 | 2016 | Aug |
TCKT138 | Closed | 30/08/2016 | 2016 | Aug |
TCKT139 | Closed | 06/09/2016 | 2016 | Sep |
TCKT140 | Closed | 06/09/2016 | 2016 | Sep |
TCKT141 | Closed | 09/09/2016 | 2016 | Sep |
TCKT142 | Open | 09/09/2016 | 2016 | Sep |
TCKT143 | Open | 12/09/2016 | 2016 | Sep |
I have a Selector with Year and Month and I need to list the Tickets from previous Months with Status = Open
For example, if I select Year = 2016 and Month = Jul, I'd see the following:
TICKET | STATUS | CREATE_DATE | YEAR | MONTH |
---|---|---|---|---|
TCKT009 | Open | 17/06/2016 | 2016 | Jun |
TCKT010 | Open | 17/06/2016 | 2016 | Jun |
TCKT029 | Open | 28/06/2016 | 2016 | Jun |
TCKT035 | Open | 29/06/2016 | 2016 | Jun |
Now if I select Year = 2016 and Month = Aug, I'd see the following:
TICKET | STATUS | CREATE_DATE | YEAR | MONTH |
---|---|---|---|---|
TCKT009 | Open | 17/06/2016 | 2016 | Jun |
TCKT010 | Open | 17/06/2016 | 2016 | Jun |
TCKT029 | Open | 28/06/2016 | 2016 | Jun |
TCKT035 | Open | 29/06/2016 | 2016 | Jun |
TCKT082 | Open | 15/07/2016 | 2016 | Jul |
TCKT083 | Open | 18/07/2016 | 2016 | Jul |
TCKT084 | Open | 18/07/2016 | 2016 | Jul |
This would be selecting Year = 2016 and Month = Sep:
TICKET | STATUS | CREATE_DATE | YEAR | MONTH |
---|---|---|---|---|
TCKT009 | Open | 17/06/2016 | 2016 | Jun |
TCKT010 | Open | 17/06/2016 | 2016 | Jun |
TCKT029 | Open | 28/06/2016 | 2016 | Jun |
TCKT035 | Open | 29/06/2016 | 2016 | Jun |
TCKT082 | Open | 15/07/2016 | 2016 | Jul |
TCKT083 | Open | 18/07/2016 | 2016 | Jul |
TCKT084 | Open | 18/07/2016 | 2016 | Jul |
TCKT101 | Open | 05/08/2016 | 2016 | Aug |
TCKT102 | Open | 05/08/2016 | 2016 | Aug |
Do you know how could I do that?
Thank you!!!
PS. I attach a QVW with the file.
Check this out -
try calculated Dimension
if (CREATE_DATE < max(CREATE_DATE), CREATE_DATE)
or if you have an Expression, use set Analysis
e.g. Count ({<CREATE_DATE={'<=$(=max(CREATE_DATE)'}>}TICKET
I tried it but it doesn't work. Could you take a look at the QVW? Thanks.
Check this out -
I can not upload your qvw
here is the epxression:
=count({<MONTH=,CREATE_DATE={'<$(=date(monthstart(max(CREATE_DATE))))'}>}TICKET)
BUT:
you need to Change your dateformat (it was M/D/YYYY, thats why it was not recognized as correct dateformat)
SET DateFormat='D/M/YYYY';