topic Re: Dynamic Calendar based off List Box in QlikView

Dynamic Calendar based off List Box

Wed, 22 Jan 2014 20:45:54 GMT

I am fairly new to QlikView and I think I am diving into advanced functions a little quick.

First Table:

SCRA_DateField_Chooser:

Load * INLINE [

TEST_ID, Date_Short, Date_Full

1, ASSIST_EFF_DATE, Assist Effective Date

1, CREATE_DATE, Create Date

1, TRAN_DATE, Transaction Date

];

Second table:

TestLoan:

Load Distinct 1 as TEST_ID,

ACCOUNT_NBR From $(qvdPath)VW_A004.qvd(qvd);

Third Table:

SCRA_04_FeesAssessed:

LOAD ACCOUNT_NBR

SCRA_04_FeesAssessed.ASSIST_EFF_DATE,

SCRA_04_FeesAssessed.TRAN_DATE,

SCRA_04_FeesAsssessd.CREATE_DATE

From

$(qvdPath)VW_A004.qvd

(qvd);

Table Joins:

TestLoan.TEST_ID to SCRA_DateField_Chooser.TEST_ID

TestLoan.ACCOUNT_NBR = SCRA_04_FeesAssessed.ACCOUNT_NBR

I then have it set up when i click on Test_ID = 1 in the tablebox from the Main Sheet, it will Launch SH02 and automatically filter TEST_ID = 1 using a Trigger on TEST_ID where I set Variables.

Here is the question: I have Created 1st Listbox that has the Date_Full names in it so the person can select which Date they want to filter by. I was then trying to create a Trigger on Date_Full that will Set Variable vSelectedDate = IF(TEST_ID = 1,'SCRA_04_FeesAssessed.' & Date_SHORT,0) which seems to work and sets my Variable to SCRA_04_FeesAssessed.CREATE_DATE.

I was then creating a second listbox that would have in the General tab/Field Expression = vSelectedDate to pull the dates from this field, but I am having no luck Error = (unavailable)[SCRA_04_FeesAssessed.CREATE_DATE]. I was essentially trying to make this into a Calendar function and create Month(vSelectedDate) or Year(vSelectedDate), so the user can select which date they want and the time frame they need.

I know I could build either a container or build listboxes for month/year in them for each of the dates, but I would think that isn't efficient, especially since I have to build 8 more identical sheets.

Thanks,

Re: Dynamic Calendar based off List Box

Tyler_Waterfall — Wed, 22 Jan 2014 21:01:03 GMT

spmansteel

You've posted a desktop development question to the Governance Dashboard space - I recommend you post this to the App Development space or the New to QlikView space.

~ Tyler

Re: Dynamic Calendar based off List Box

rustyfishbones — Wed, 22 Jan 2014 23:16:29 GMT

twa

Why can't the question be answered by you Tyler, everyday people help on this community, which is fantastic, but we all do this for the pride and pleasure of knowing we have helped a fellow Qlikview user solve their Qlikview problems.

I find it quite annoying and disrespectful to everyone who contributes their time and effort to help each other on Qlik Community, that someone who works for Qliktech simply diverts the person asking a question to a different "space", not everyone asking a question understands what "space" their question should be asked to.

Your mindset is not something I would expect from a Qliktech employee.

If you don't know the answer to the question, ask one of your Qliktech colleagues and then respond to it, otherwise, don't answer it all, as I'm sure it will be picked up by someone who actually cares, unlike you it seems.

spmansteel, I apologize, if I could help you with this issue I would but unfortunately I don't have enough knowledge.

best of luck

This community is fantastic, so I am sure you will get the answer you need.

You can see some videos here Alan Farrell - YouTube

Re: Dynamic Calendar based off List Box

Tyler_Waterfall — Thu, 23 Jan 2014 11:41:46 GMT

Sorry for not being more help here! Limited by my own expertise in this area. The broader community will have that expertise!

Tyler

Re: Dynamic Calendar based off List Box

Thu, 23 Jan 2014 15:19:26 GMT

I have figured out an alternative that seems to be working. I have created my 2nd listbox in the general tab/Expression set that

if(Full_Name='CREATE_Date',Year(Date(SCRA_04_FeesAssessed.CREATE_Date)),

If(Full_Name='TRANS_DATE',Year(Date(SCRA_04_FeesAssessed.TRANS_Date)),

If(Full_Name='ASSIST_EFF_DATE',Year(Date(SCRA_04_FeesAssessed.ASSIST_EFF_DATE)),"")))

If anyone know's a more efficient way of doing this then please let me know.

Thanks,

Re: Dynamic Calendar based off List Box

Tyler_Waterfall — Fri, 31 Jan 2014 21:10:29 GMT

Ouch!

Re: Dynamic Calendar based off List Box

rustyfishbones — Fri, 31 Jan 2014 21:20:05 GMT

Dear Tyler,

Upon reflection, I was very harsh, I was having a bad day that day to be honest

Please accept my apologies

Enjoy your weekend

Regards

Alan

Re: Dynamic Calendar based off List Box

Tyler_Waterfall — Fri, 31 Jan 2014 21:25:45 GMT

No worries. Enjoy your weekend as well. Wahoo!

Tyler

Re: Dynamic Calendar based off List Box

rustyfishbones — Fri, 31 Jan 2014 21:29:42 GMT

Super Bowl weekend too!

Enjoy the ads, I believe U2 will have an add for their new song

All the Best

Re: Dynamic Calendar based off List Box

Anonymous — Fri, 31 Jan 2014 21:35:59 GMT

In the Script make a Master Calendar for each date

Year(SCRA_04_FeesAssessed.CREATE_Date) as CreatedYear

Month(SCRA_04_FeesAssessed.CREATE_Date) as CreatedMonth

Date(SCRA_04_FeesAssessed.CREATE_Date) as CreatedDate

Year(SCRA_04_FeesAssessed.TRANS_Date) as TransYear

Month(SCRA_04_FeesAssessed.TRANS_Date) as TransMonth

Date(SCRA_04_FeesAssessed.TRANS_Date) as TransDate

etc....

Then create and Inline Table called Calendar

Calendars:

LOAD * INLINE [

Calendar

Created

Trans

Assist

];

In the application make 4 list boxes. One will be the Calendar and the other 3 as expressions.

The three expressions each an individual listbox are:

=[$(=Calendar)Year]

=[$(=Calendar)Month]

=[$(=Calendar)Date]

Your 3 expression list boxes will now change dynamically based on what Calendar you select in the Calendar list box. I hope this is what you were looking for.

-Mike

Re: Dynamic Calendar based off List Box

Tue, 25 Feb 2014 13:56:09 GMT

Thanks Michael for you reply. I tried this with the TransDate first to make sure it works and it did.

Thanks very much this is exactly what I was trying to do.