topic Re: Min Date as List Box in QlikView

Min Date as List Box

cbaqir — Fri, 22 May 2015 15:40:30 GMT

In my FACT table, I have REQUEST_ID, TRX_DATE and WORKFLOW_STEP_START_DATE (this one applies to each workflow step).

TRX_DATE joins to my calendar table to return Month/Year (among other things).

In a DIMENSION table, I have REQUEST_ID and REQ_SUBMIT_DATE.

I have a requirement for the user to be able to filter based on Month/Year of Request Submission (REQ_SUBMIT_DATE).

I tried adding a list box for REQ_SUBMIT_DATE but without the join to the Calendar table, I get multiple rows returned for the same month/year.

I tried min( "WORKFLOW_STEP_START_DATE") as TRX_DATE and
I tried date( min("WORKFLOW_STEP_START_DATE"), 'YYYY-MMM') as CREATE_MY, but I got errors when trying to load the data.

Any ideas? Thanks,

Cassandra

Re: Min Date as List Box

Anonymous — Fri, 22 May 2015 15:47:30 GMT

I think you need

monthstart( "WORKFLOW_STEP_START_DATE") as TRX_DATE

Re: Min Date as List Box

Qrishna — Fri, 22 May 2015 15:49:52 GMT

Hi Cassandra,

Min() returns just one value and min( "WORKFLOW_STEP_START_DATE") as TRX_DATE returns just min date (a single ) in the field 'REQ_SUBMIT_DATE'.

if you want to pull out the the months and years of REQ_SUBMIT_DATE into a list box as per you requirement,

try using Month( REQ_SUBMIT_DATE) and year(REQ_SUBMIT_DATE ) if you did not include request submit dates which creating your calendar.

Let me know if you have any questions.

Thanks

krishna

Re: Min Date as List Box

cbaqir — Fri, 22 May 2015 16:38:25 GMT

I'm using Year then Month to display 2015-May. How would I then get the List Box to sort appropriately (asc date)?

Re: Min Date as List Box

Anonymous — Fri, 22 May 2015 16:46:26 GMT

date(monthstart( "WORKFLOW_STEP_START_DATE"), 'YYYY-MMM')

And use "Sort" tab in Properties

Re: Min Date as List Box

Qrishna — Fri, 22 May 2015 16:49:53 GMT

use

Subfield(year-month,'-',1) as year in script or Subfield(year-month,'-',1) in the list box expression

Subfield(year-month,'-',2) as month in script or Subfield(year-month,'-',2) in the list box expression

eg : Subfield(2015-May,'-',1) returns 2015

Subfield(2015-May,'-',2) returns May

after the values are populated in a list box, goto list box props -->sort --> your preferred sort

just like the way in the below screenshot

hope that helps.

Thanks

Re: Min Date as List Box

cbaqir — Fri, 22 May 2015 17:28:52 GMT

Thanks!

Re: Min Date as List Box

cbaqir — Fri, 22 May 2015 17:28:59 GMT