topic Re: Master calendar month number issue in QlikView

Master calendar month number issue

arpitkharkia — Tue, 23 Jan 2018 17:40:29 GMT

Hi all,

I have a master calendar ranging from Jan-2017 to December-2018. I have generated month number for all the 24 months. The problem i am facing is that Max(MonthNumber) gives me Dec-18, but i want to point at the current month that is Jan-18.

Is there a way i can identify the month number of current month which i can use in set analysis?

Thanks!

Re: Master calendar month number issue

sunny_talwar — Tue, 23 Jan 2018 17:41:44 GMT

May be like this

Date(MonthStart(Today()), 'MMM-YY')

Re: Master calendar month number issue

arpitkharkia — Tue, 23 Jan 2018 17:47:17 GMT

Thanks for the quick reply.

I am using the same thing for current month, and for previous months i am just subtracting from it and getting the required months. But consider the following scenario:

I need to know the

1. sum(sales) - Current month

2. sum(sales) - Previous month

3. sum(sales) - All the rest of the previous months.

The third point is where i am facing problem

Re: Master calendar month number issue

darrell_tobin — Tue, 23 Jan 2018 17:49:23 GMT

You could try this..

num( month(<Date>) ) ) // will return 1 for Jan 18

=Year (<Date>) *100+ num(month(<Date>)) // willl return 201801

Thanks D

🙂

Re: Master calendar month number issue

sunny_talwar — Tue, 23 Jan 2018 17:50:38 GMT

May be like this

Sum({<MonthYearField= {"$(='<=' & Date(MonthStart(Today()), 'MMM-YY'))"}>}Sales)

Re: Master calendar month number issue

darrell_tobin — Tue, 23 Jan 2018 17:51:35 GMT

If you want current then use now() instead of <date>

You could try this..

num( month(now()) ) ) // will return 1 for Jan 18

=Year (now()) *100+ num(month(now())) // willl return 201801

Thanks D

🙂

Re: Master calendar month number issue

darrell_tobin — Tue, 23 Jan 2018 18:11:21 GMT

Hi Arpit,

In all my reports with dates fields, I create indexes such as a master field called Month_index using below:

= (

Num( Year(Date) ) * 12 + Num( Month(Date) )

) // Creates month number for each number

(

Num( Year(Now()) ) * 12 + Num( Month(Now()) )

)// Creates month number for this month

This counts backwards from current month

0 = current month

-1 = last month

-2 = previous month

etc

means you can then use simple set analysis such as

sum( Sales {$<Month_index = '0'>}) //current month

sum( Sales {$<Month_index = '-1'>}) // previous month

sum( Sales {$<Month_index = { '<-1'}>}) // All other previous months

Same can be applied to weeks and years etc...

Thanks

🙂

Re: Master calendar month number issue

arpitkharkia — Wed, 24 Jan 2018 04:20:19 GMT

Thank u so much Sunny. Its working just fine!

Re: Master calendar month number issue

arpitkharkia — Wed, 24 Jan 2018 04:20:55 GMT

Thanks for the reply Darrell. This one may also work fine!

Re: Master calendar month number issue

arpitkharkia — Wed, 24 Jan 2018 06:11:50 GMT

Is it possible to select month ranges here?

Like 0-4 months,5-6 months, where

0-Jan-18

1-Dec-17

2-Nov-17 and so on?

Re: Master calendar month number issue

sunny_talwar — Wed, 24 Jan 2018 10:09:21 GMT

Yes, it should be... but select month ranges within the expression or for selections?

Re: Master calendar month number issue

arpitkharkia — Wed, 24 Jan 2018 10:33:14 GMT

Within expressions. Actually i have done something and its working.

Any alternate/efficient way would be appreciated!

Re: Master calendar month number issue

sunny_talwar — Wed, 24 Jan 2018 12:06:05 GMT

What have you done? May be we can make changes to what you already have? Will also give me an idea of what you might be looking for

Re: Master calendar month number issue

sunny_talwar — Wed, 24 Jan 2018 12:06:40 GMT

Also, once you have what you needed, please close the thread by marking correct and helpful responses.

Best,

Sunny