Min Date as List Box

[cbaqir]

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

[Anonymous]

I think you need

monthstart( "WORKFLOW_STEP_START_DATE") as TRX_DATE

[Qrishna]

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

[cbaqir]

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

[Anonymous · Accepted solution]

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

And use "Sort" tab in Properties

[Qrishna]

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

[cbaqir]

Thanks!

[cbaqir]

Thanks