topic Re: Trailing Fiscal Year Month Count in QlikView

Trailing Fiscal Year Month Count

proctors — Wed, 25 Nov 2020 16:16:04 GMT

Hi. I have a productivity gauge that tells a user how close they are to meeting their goal. For example the numerator is the sum of their units and the denominator is their total expected units for the year. One suggestion a user had was can the calculation determine how productive they are until that current point in time.

For example, if we are 4 months into the Fiscal Year, the denominator should only be the total expected within four months. Two months from now, the user should see their goal % for 6 months of work and so on. Thus, it's tied to the current date and counting back.

How can I make a variable/expression that looks at the current fiscal year selection [FY Year] and determines how many months we are into the fiscal year? On the other hand, if they select 2017, the denominator should be 12 because there are no more remaining months in the fiscal year.

=Sum([RVU WORK])/vThresholdGoal

vThresholdGoal = 1,900

Re: Trailing Fiscal Year Month Count

Thiago_Justen_ — Wed, 24 Jan 2018 00:10:04 GMT

I'm not sure if I got your point, but let's try doing something. Firstly:

=Sum([RVU WORK])/($(vThresholdGoal))

Then, if you want to get just the number of elapsed months you should try this out:

Max (Month(YOUR_DATE_FIELD))

Cheers

Re: Trailing Fiscal Year Month Count

proctors — Wed, 24 Jan 2018 14:31:50 GMT

Thanks, this works for FY 2018 and gives me 7. However, when I then select FY 2017, the answer stays as 7, but should be 12 because the FY is complete.. Is there any way for it to see how many months are left per fiscal year?

Re: Trailing Fiscal Year Month Count

Thiago_Justen_ — Wed, 24 Jan 2018 14:57:05 GMT

What date Field are you using? What is its format?

Could you give me a sample file?

Re: Trailing Fiscal Year Month Count

proctors — Wed, 24 Jan 2018 15:15:30 GMT

Sure, how can I share it?

Re: Trailing Fiscal Year Month Count

Thiago_Justen_ — Wed, 24 Jan 2018 15:19:06 GMT

Follow these pictures below:

First:

Second: