topic Re: How to calculate Month Count for Fiscal Periods in QlikView

How to calculate Month Count for Fiscal Periods

Jesh19 — Wed, 23 May 2018 12:43:06 GMT

Hi All,

I am having Fiscal Period Starting from Apr to Mar

So, in a particular fiscal period, i need to get the count of months.

For example :

My Fiscal Period is something like this:

Apr 18, May 18, Jun 18....Mar 19

If i select Apr 18, my month Count should be 1

May 18, count should be 2

Mar 19 , count value should be 12.

Could someone please help me in getting the month count value.

Thanking in Advance.

Regards,

Jeshwanth B

Re: How to calculate Month Count for Fiscal Periods

sunny_talwar — Wed, 23 May 2018 12:57:32 GMT

Would you be able to share some raw data of what you have and elaborate a little bit more on what you are trying to get?

Re: How to calculate Month Count for Fiscal Periods

shansundar — Wed, 23 May 2018 13:05:55 GMT

You can calculate the monthnum based on the difference between the month selected -3, like addmonths (Month selected,-3).

So what happens when user selects Apr2018 then the count value will be 4-3 = 1, similarly for May2018 5-3 =2 and so on.

Thanks,

Shan S

Re: How to calculate Month Count for Fiscal Periods

Jesh19 — Wed, 23 May 2018 13:06:32 GMT

Hi Sunny,

See the below screenshot.

From the above screenshot, you can see the selected period is May 17 and my count in the text object should be 2

Similarly for all the respective months, the count should be displayed.

when i select Apr 17, it should be 1

May 17, it should be 2

Mar 18, it should be 13

The same should be again repeated for next fiscal year, i.e., from Apr 18 to Mar 19.

Thanks & Regards,

Jeshwanth B

Re: How to calculate Month Count for Fiscal Periods

sunny_talwar — Wed, 23 May 2018 13:08:25 GMT

May be this

Count(DISTINCT {<MonthYear = {"$(='<=' & Max(MonthYear))"}, FiscalYear = p(FiscalYear)>}MonthYear)

Re: How to calculate Month Count for Fiscal Periods

Jesh19 — Wed, 23 May 2018 13:15:37 GMT

It's working.

Thanks a lot

Re: How to calculate Month Count for Fiscal Periods

shansundar — Wed, 23 May 2018 13:17:06 GMT

You're welcome, happy Qliking..

Re: How to calculate Month Count for Fiscal Periods

johnca — Wed, 23 May 2018 18:13:33 GMT

Please mark the correct answer as Answered so other people with a similar issue will know, and people like myself don't look into a thread hoping to help but finding it already answered.