topic Re: Set Analysis Date Ranges in QlikView

Set Analysis Date Ranges

Wed, 20 Apr 2016 12:13:05 GMT

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

Re: Set Analysis Date Ranges

sunny_talwar — Wed, 20 Apr 2016 12:18:25 GMT

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

Re: Set Analysis Date Ranges

Wed, 20 Apr 2016 13:21:24 GMT

Hi,

I have attached the sample file.

Thanks,

Re: Set Analysis Date Ranges

sunny_talwar — Wed, 20 Apr 2016 13:38:44 GMT

May be this?

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

Re: Set Analysis Date Ranges

Thu, 21 Apr 2016 04:59:32 GMT

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

Re: Set Analysis Date Ranges

sunny_talwar — Thu, 21 Apr 2016 08:10:53 GMT

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?

Re: Set Analysis Date Ranges

nico_ilog — Thu, 21 Apr 2016 08:40:30 GMT

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

Re: Set Analysis Date Ranges

perumal_41 — Thu, 21 Apr 2016 09:05:21 GMT

let me know always select two years ?

Re: Set Analysis Date Ranges

Thu, 21 Apr 2016 09:13:36 GMT

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

Re: Set Analysis Date Ranges

Thu, 21 Apr 2016 10:35:49 GMT

Yes, two years will be selected all the time

Re: Set Analysis Date Ranges

Mon, 25 Apr 2016 09:55:51 GMT

Hi,

Attached is the sample qvw and I my exact requirement for your reference.

I want the Attrition% for Current Month Current Year, Previous Month Previous Year.

Let me know if this is not clear.

Thanks,

Vidhya

Re: Set Analysis Date Ranges

nico_ilog — Mon, 25 Apr 2016 10:23:10 GMT

Something like the attached?

Re: Set Analysis Date Ranges

Mon, 25 Apr 2016 10:46:23 GMT

I want the current month numbers to be displayed by default, when the dashboard is opened.

If 2016 n 2015 are selected, I should see 2016 April and 2015 April numbers in CY and PY columns respectively.

Re: Set Analysis Date Ranges

Mon, 25 Apr 2016 12:20:41 GMT

I have used the following expression.

num(sum({<Date={">=$(vMinDate)<=$(vMaxDate)"},FIN_YEAR_NO={"$(vMaxYear)"},FIN_MONTH_NO={"$(vMaxMonth)"}>}Exit_Count)/

((sum({<FIN_YEAR_NO={"$(vMaxYear)"},FIN_MONTH_NO={"$(vMaxMonth)"}>}OpeningBalance)+

sum({<FIN_YEAR_NO={"$(vMaxYear)"},FIN_MONTH_NO={"$(vMaxMonth)"}>}ClosingBalance))/2),'##.#%')

If I select 2017 and 2016, I get "no data to display" for current year and for 2016 I get Mar numbers, which is ideally wrong.

I should get April for both 2017 and 2016

Re: Set Analysis Date Ranges

sunny_talwar — Mon, 25 Apr 2016 12:36:51 GMT

Where are you looking to get these numbers? Straight table or text box objects? Is the image in the doc file the number you would see when you select 2015 and 2016? I am not sure what exactly are you looking to do. Please elaborate

Re: Set Analysis Date Ranges

Tue, 26 Apr 2016 04:29:16 GMT

I want these numbers to be displayed in Gauge object, style being digital display as seen in the screenshot.

If 2016 and 2015 are selected, numbers will be seen in the object. Let me know if this is clear

Re: Set Analysis Date Ranges

Wed, 27 Apr 2016 10:14:48 GMT

Thanks all for your help.

I would like to close this thread, as my problem is solved now.

I had to change my variable to

max({<FIN_YEAR_NO={"$(vMaxYear)"}>}FIN_MONTH_NO)

in order to fix the issue

Rgds,

Vidhya