Show Previous Months Rows

microwin88x — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Straight Table: Show Rows from Previous Months

Anonymous — Wed, 28 Sep 2016 13:50:46 GMT

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

Re: Straight Table: Show Rows from Previous Months

microwin88x — Wed, 28 Sep 2016 14:37:57 GMT

I tried it but it doesn't work. Could you take a look at the QVW? Thanks.

Re: Straight Table: Show Rows from Previous Months

Digvijay_Singh — Wed, 28 Sep 2016 14:43:32 GMT

Check this out -

Re: Show Previous Months Rows

Anonymous — Wed, 28 Sep 2016 14:52:50 GMT

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

topic Re: Straight Table: Show Rows from Previous Months in QlikView

Show Previous Months Rows

Re: Straight Table: Show Rows from Previous Months

Re: Straight Table: Show Rows from Previous Months

Re: Straight Table: Show Rows from Previous Months

Re: Show Previous Months Rows