Set Analysis Date Ranges

[#1]

Hello all,

I face the following issue in my application.

I have to calculate Exit count for HR report

I use the formula

sum({<Date={">=$(vMinDate)<=$(vMaxDate)"}>}Exit_Count)

I need to show the current month numbers for the current year and the previous year.

For example, if  2015 - 2016 (2016) and 2014 - 2015 (2015) are selected, then by default April numbers should be seen for both years.

If user selects, any other month, then accordingly month should change.

How to apply a filter of MaxMonth in set analysis in my expression above??

Thanks in advance,

Sheela

[sunny_talwar]

Would you be able to share a sample with your expected output along with it?

[#3]

Hi,

I have attached the sample file.

Thanks,

VK

[sunny_talwar]

May be this?

Sum({<Date={"$(='>=' & MonthStart(Max({<Year = {$(=Max(Year))}>}Date)) & '<=' & MonthEnd(Max({<Year = {$(=Max(Year))}>}Date)))"}>}EC)

[#5]

Hi,

Thanks for your repli.

My formula for Attrition% is

sum({<Date={">=$(vMinDate)<=$(vMaxDate)"}>}Exit_Count)/
(((sum({<Date={"$(vMinDate)"}>}OpeningBalance)+sum({<Date={"$(vMaxDate)"}>}ClosingBalance))/2))

So, I have to apply month filter in such a way, that it suppresses date condition.

If I give Monthstart for Opening Banlance and MonthEnd for CLosing Balance, the formula you suggested doesn't seem to work.

Can you help out?

Thanks,

Sheela

[sunny_talwar]

The application you have shared doesn't seem to have Opening and ClosingBalance field. I am not sure how to see what you are trying to achieve her. Can you update the sample and clarify the issue?

[nico_ilog]

Hi,

Try this.

Variables:

vMaxDate =Date(max(YourDate)

vMaxDay =Day(max(YourDate))

vMaxMonth =Month(max(YourDate))

vMaxYear = year(Max(YourDate))

//These variables are actually used on my side for MAT, bu i ammended the addmonths to only subtract 1 month (see in red). So you can change that to show not only the last 1 month, but 2,3,4,5,6 etc.

//Variables for This Year

vStartDateTY =Date(MakeDate(Year(addmonths(vEndDateTY ,-1)),Month(addmonths(vEndDateTY ,-1)),1))

vEndDateTY =Date(vMaxDate)

//Variables for Last Year

vStartDateLY =Date(MakeDate(Year(addmonths(vEndDateLY ,-1)),Month(addmonths(vEndDateLY ,-1)),1))

vEndDateLY =Date(AddYears(vMaxDate,-1))

Expression:

for THIS YEAR =(SUM({$<YourDate={'>=$(vStartDateTY)=$(vEndDateTY)'}>}(YourField))

for LASTYEAR =(SUM({$<YourDate={'>=$(vStartDateLY)=$(vEndDateLY)'}>}(YourField))

LAST NOTE: My Date format is dd/MM/yyyy

This way it will automatically use the MAX month if none other is selected, and you will have TY vs LY in the same instance. I Cannot implement this on you Testing.qvw because you do not have enough data (proper Dates - Your Month field,is not recognized as a month by the QV application, it is derived text only).

Hope this helps.

Nico

[perumal_41]

let me know always select two years ?

[#9]

Hi,

I would rather determine the period to select in your script and add rather simple expressions in your charts something like:

Expressions:

THIS YEAR =(SUM({$<YearInd={'0'}>}YourField)

LASTYEAR =(SUM({$<YearInd={'1'}>}YourField)

where YearInd = 0 indicates current year and YearInd = 1 indicates last year

HtH

P.

[#10]

Yes, two years will be selected all the time