Set analysis - Something ismilar to PriorYear

[Edvin]

Hey guys,

I have a pivot table, where I can observe changes in some values.

To be more clear, I am using set analysis to calculate YearToYear values, based on chosen year.

For instance, if I choose 2017 and 2019 year, then it will calculate for those years.

More, if I chose 2017 and 2019 years and January-July (01-07) months, so it will calculate based on my selection.

My set analysis is based on variables with MinDate and MaxDate logic.

Set analysis for the "bigger year" (for example 2018 vs. 2017 - so the "bigger year" is 2018):

count({<[YearField]={'$(vMaxYear)'},[DateField]={'<=$(vMaxDate)'},[MonthField]={'<=$(vMaxMonthNum)'},[CaseSubcaseStatus]={'CL'}>} [CaseNo])

For the "smaller year" as mentioned above I just change vMaxYear with vMinYear

Now if I want for example to observe values of the 2nd Quarter for 2017 and 2018 years. I need to choose April-June(04-06) months. But the set analysis counts still from the 1st Month. I tried to implement new variable, but it still counts wrong - counts from the 1st month of the year.

This is my "new" set expression (added vMinMonthNum):

count({<[YearField]={'$(vMaxYear)'},[DateField]={'<=$(vMaxDate)'},[MonthField]={'<=$(vMaxMonthNum)'},[MonthField]={'>=$(vMinMonthNum)'},[CaseSubcaseStatus]={'CL'}>} [CaseNo])

Thanks in advance.

[dplr-rn · Accepted solution]

try this

count({<[YearField]={'$(vMaxYear)'},[DateField]={'<=$(vMaxDate)'},[MonthField]={">=$(vMinMonthNum)<=$(vMaxMonthNum)"},[CaseSubcaseStatus]={'CL'}>} [CaseNo])

[Edvin]

You're a god. Thanks!