Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

proctors
Contributor

Trailing Fiscal Year Month Count

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

1 Solution

Accepted Solutions
thiago_justen
Valued Contributor III

Re: Trailing Fiscal Year Month Count

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

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
5 Replies
thiago_justen
Valued Contributor III

Re: Trailing Fiscal Year Month Count

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

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
proctors
Contributor

Re: Trailing Fiscal Year Month Count

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?

thiago_justen
Valued Contributor III

Re: Trailing Fiscal Year Month Count

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

Could you give me a sample file?

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago
proctors
Contributor

Re: Trailing Fiscal Year Month Count

Sure, how can I share it?

thiago_justen
Valued Contributor III

Re: Trailing Fiscal Year Month Count

Follow these pictures below:

First:

Capturar.PNG

Second:

Capturar2.PNG

Thiago Justen Teixeira Gonçalves
WhatsApp: 24 98152-1675
Skype: justen.thiago