topic Re: How to get three Consecutive months in QlikView

How to get three months

NavinReddy — Fri, 11 Mar 2016 12:57:14 GMT

Hi Dear Experts,

I have month column in my fields list.

Here based on Month selections i have to display three Consecutive Months in pivot table

Here is my Month filed Current im using "Month(MonthStart(Date(Mid([MTRC_FCT_DT],1,8)))) as Month"

Any suggestions how to achieve this

Ex:

if Month= Jan (Month-2, Month-1,Month)

O/P= Nov, Dec,Jan

if Month= Feb (Month-2, Month-1,Month)

O/P= Dec,Jan, Feb

if Month= Mar, (Month-2, Month-1,Month)

O/P= Jan, Feb,Mar

Best Regards,

Niranjan

Re: How to get three Consecutive months

sunny_talwar — Fri, 11 Mar 2016 13:00:32 GMT

You can either use set analysis or use the concept of As-of Table: The As-Of Table

Re: How to get three Consecutive months

Gysbert_Wassenaar — Fri, 11 Mar 2016 13:04:46 GMT

Won't work if you only have the month numbers. 1 - 2 = -1 and not 12. So you need a date field like Date(MonthStart(Date(Mid([MTRC_FCT_DT],1,8))),'MMM YY') as MonthYear. Then you can select the 3-month period in your chart expressions. For example sum(Amount) would become sum({<Month=, MonthYear={">=$(=MonthStart(max(MonthYear),-2))<=$(=max(MonthYear))"}>}Amount)

Re: How to get three Consecutive months

NavinReddy — Fri, 11 Mar 2016 19:13:44 GMT

Hi Gysbert,

Thanks for your reply

=Count({<Month=, MonthYear={">=$(=MonthStart(max(MonthYear),-2))<=$(=max(MonthYear))"}>}RATING_CB)

with this expression i got the below result, can't we restrict to display only three months data

any suggestions

Best Regards,

Niranjan

Re: How to get three Consecutive months

Digvijay_Singh — Fri, 11 Mar 2016 19:22:01 GMT

Pl Chk the value of set expression in the header of expression column, smthg might be wrong else expression shouldn't show more than 3 months.

Re: How to get three Consecutive months

Gysbert_Wassenaar — Sun, 13 Mar 2016 14:43:39 GMT

That result make no sense at all. Can you post a small qlikview document that demonstrates the problem?

Re: How to get three Consecutive months

NavinReddy — Mon, 14 Mar 2016 14:29:33 GMT

Hi Gysbert,

Sorry for my late reply, i have attached sample document

Here based on month selections in pivot i have to display 3months of data

Ex:

if Month= Jan (Month-2, Month-1,Month)

O/P= Nov, Dec,Jan

Best Regards,

Niranjan

Re: How to get three Consecutive months

Gysbert_Wassenaar — Mon, 14 Mar 2016 16:24:50 GMT

Try this expression:

=Count({<Month=, MonthYear={">=$(=date(MonthStart(max(MonthYear),-2),'MMM YY'))<=$(=Date(max(MonthYear),'MMM YY'))"}>}RAG_RATING_CD)

Re: How to get three Consecutive months

NavinReddy — Mon, 14 Mar 2016 16:42:31 GMT

Hi Gysbert,

its working fine, But if can i clear the filters chart its not displaying any data

how can i make it current month as default selection

Best Regards,

Niranjan

Re: How to get three Consecutive months

Gysbert_Wassenaar — Mon, 14 Mar 2016 16:57:54 GMT

Create a variable vMonth: =if(len(GetCurrentSelections()),max(MonthYear),MonthStart(Today()))

Change the expression to =Count({<Month=, MonthYear={">=$(=date(MonthStart($(vMonth),-2),'MMM YY'))<=$(=Date($(vMonth),'MMM YY'))"}>}RAG_RATING_CD)

Re: How to get three Consecutive months

NavinReddy — Wed, 16 Mar 2016 14:56:01 GMT

Thanks Gysbert

Re: How to get three Consecutive months

NavinReddy — Fri, 18 Mar 2016 11:01:11 GMT

Hi Gysbert,

Please help me below if condition im not getting exact Month selections

for all the months im getting exact same result, please suggest me where im doing wrong

vMonth: =if(len(GetCurrentSelections()),max(MonthYear),MonthStart(Today()))

=if(Date($(vMonth),'MMM YY'), if ((RAG_RATING_CD) = 'R', 'Tier1',

if ((RAG_RATING_CD) = 'A', 'Tier2',

if ((RAG_RATING_CD) = 'G', 'Tier3'))),

if(Date($(vMonth)-10,'MMM YY'), if ((RAG_RATING_CD) = 'R', 'Tier1',

if ((RAG_RATING_CD) = 'A', 'Tier2',

if ((RAG_RATING_CD) = 'G', 'Tier3'))),

if(Date($(vMonth)-50,'MMM YY'), if ((RAG_RATING_CD) = 'R', 'Tier1',

if ((RAG_RATING_CD) = 'A', 'Tier2',

if ((RAG_RATING_CD) = 'G', 'Tier3'))))))

Best Regards,

Niranjan

Re: How to get three Consecutive months

Gysbert_Wassenaar — Fri, 18 Mar 2016 11:12:45 GMT

Please start a new discussion that explains what you're trying to do. I have no idea what that is.

Re: How to get three months

NavinReddy — Mon, 21 Mar 2016 11:11:20 GMT

Hi Gysberrt,

Good Morning,

Here the query i have posted, i tried with different ways but im not getting

based on MonthYear selections for three months im calculating the Tiers

But all months im getting the same result

for your reference i have attached sample application

month variable not working For all months i got the same results

Best Regards,

Niranjan