topic Re: Default selection- Last 3 months in QlikView

Default selection- Last 3 months

Fri, 17 Jan 2014 05:04:59 GMT

Following is the requirement Default view should show last 3 completed months data. For example, if today’s date is 23-Oct then data of June ,July and Aug should be displayed as part of selection list box. Currently data is available for 9 months

Month-Year

May-13

Jun-13

Jul-13

Aug-13

Sep-13

Oct-13

Nov-13

Dec-13

Please suggest with an example..

Re: Default selection- Last 3 months

tresB — Fri, 17 Jan 2014 05:07:29 GMT

Please take the pain of ceating an example so that people here can help you out faster.

Re: Default selection- Last 3 months

jonathandienst — Fri, 17 Jan 2014 05:08:50 GMT

You could use set analysis like

{<Date = {">=$(=AddMonths(Today(), -3))""}>}

HTH

Jonathan

Re: Default selection- Last 3 months

Fri, 17 Jan 2014 05:13:53 GMT

I need to implement this as part of triggers as it should work with on open sheet

Re: Default selection- Last 3 months

jagan — Fri, 17 Jan 2014 05:19:33 GMT

HI,

If you have a date field in you app then use this expression

=Sum({<DateDimensionName={'>=$(=MonthStart(Max(DateDimensionName), -3))<=$(=MonthEnd(Max(DateDimensionName), -1))'}, MonthDimensionName=, YearDimensionName=>} MeasureName)

=Sum({<DateDimensionName={'>=$(=MonthStart(Today(), -3))<=$(=MonthEnd(Today(), -1))'}, MonthDimensionName=, YearDimensionName=>} MeasureName)

Hope this helps you.

Regards,

Jagan.

Re: Default selection- Last 3 months

kiranmanoharrode — Fri, 17 Jan 2014 06:32:30 GMT

Dear Sravanthi,

Go to Settings---- Document Settings------- Triggers------ Document Event Triggers-----On Open------Edit Action-----

Add----Selection-----Select in field

(Repeat this 3 times to add 3 triggers)

Enter your TextMonth Field in Field input box without = (sign) for ex. CommonMonth values-(APR,MAY,JUN,JUL)

then put below expression for search string input box with = (Sign)

for 1st trigger

=(Today()=MonthEnd(Today()),Date(Addmonths(Today(),-1),'MMM'),Date(Addmonths(Today(),-2),'MMM'))

for 2nd trigger

=(Today()=MonthEnd(Today()),Date(Addmonths(Today(),-1),'MMM'),Date(Addmonths(Today(),-3),'MMM'))

for 3rd Trigger

=(Today()=MonthEnd(Today()),Date(Addmonths(Today(),-1),'MMM'),Date(Addmonths(Today(),-4),'MMM'))

Save your document and reopen it.

Regards

Kiran Rode

+91 8976977897

Re: Default selection- Last 3 months

Fri, 17 Jan 2014 07:12:36 GMT

Hi Kiran,

3 rd trigger is replacing previous two triggers and only one month is selected....Requirement is to select all three months.....

Re: Default selection- Last 3 months

jagan — Fri, 17 Jan 2014 07:25:20 GMT

Hi,

Instead of 3 triggers use on one trigger and use this expression

='(' & Date(Addmonths(Today(),-1),'MMM') & '|' & Date(Addmonths(Today(),-2),'MMM') & '|' & Date(Addmonths(Today(),-3),'MMM') & ')'

Regards,

Jagan.

Re: Default selection- Last 3 months

Fri, 17 Jan 2014 07:39:57 GMT

It did not work....

Re: Default selection- Last 3 months

jagan — Fri, 17 Jan 2014 07:53:27 GMT

Hi,

Please find attached file hope it helps you. If it not works then attach sample file.

Regards,

Jagan.

Re: Re: Default selection- Last 3 months

Fri, 17 Jan 2014 07:57:48 GMT

Attached example....On Open following month should b selected in this case Dec 13,Nov 13, Oct 13

Re: Default selection- Last 3 months

jagan — Fri, 17 Jan 2014 09:00:37 GMT

Hi,

I think the space in the month causing the issue, can you remove the space in month field and then use this expression

='(' & Date(Addmonths(Today(),-1),'MMMYY') & '|' & Date(Addmonths(Today(),-2),'MMMYY') & '|' & Date(Addmonths(Today(),-3),'MMMYY') & ')'

Regards,

Jagan.

Re: Default selection- Last 3 months

tresB — Fri, 17 Jan 2014 09:17:44 GMT

Try this:

='(' &chr(34)& Date(Addmonths(Today(),-1),'MMM YY') &chr(34)& '|' &chr(34)& Date(Addmonths(Today(),-2),'MMM YY') & chr(34)&'|' &chr(34)& Date(Addmonths(Today(),-3),'MMM YY') &chr(34)& ')'

Note: space is handled using double quotes- chr(34)

Re: Default selection- Last 3 months

jagan — Fri, 17 Jan 2014 09:21:46 GMT

Hi,

Great. Tried with single quote and [ but missed double quotes.

Regards,

Jagan.

Re: Default selection- Last 3 months

Fri, 17 Jan 2014 09:26:50 GMT

Thanks Jagan