topic Re: Current Financial Year & Previous Financial Year in QlikView

Current Financial Year & Previous Financial Year

vardhancse — Wed, 01 Feb 2017 06:19:27 GMT

Hi,

I have one field called [Financial Year]

data for that field was:

FY15

FY16

FY17

Based on selection of Financial Year field, bar chart should display the current year months and previous year months field.

Could any one please let me know the condition for the same.

Tried as below in expression:

Previous Year months:

=Sum({<Month=,Year= {$(= max(Year-1))}>}[Total Cost (actual)])

Current Year Months:

=Sum({<Month=,Year= {$(=max(Year))}>}[Total Cost (actual)])

But no luck in getting as required.

Re: Current Financial Year & Previous Financial Year

kaushiknsolanki — Wed, 01 Feb 2017 06:24:31 GMT

Hi,

You need to convert your financial year into number first and then the given expression will work.

Try this.

Load [Financial Year],

Year(Makedate('20'&right([Financial Year],2)) as Year,

From Xyz;

Regards,

Kaushik Solanki

Re: Current Financial Year & Previous Financial Year

vardhancse — Wed, 01 Feb 2017 06:40:05 GMT

Thank you for your reply.

Both Financial Year and Year fields are coming from a master calendar.

Both the expressions are giving same value in my bar chart.

could you please let me know any alternative for the same.

Re: Current Financial Year & Previous Financial Year

Anil_Babu_Samineni — Wed, 01 Feb 2017 06:50:15 GMT

As Required means, Data points showing wrong you mean to say

May be missing Single Colon

Previous Year months:

=Sum({<Year= {'$(= max(Year-1))'}>}[Total Cost (actual)])

Current Year Months:

=Sum({<Year= {'$(=max(Year))'}>}[Total Cost (actual)])

If not, You may share your application to look in

kaushik.solanki‌ What if, Year has 1900,1901,----1999

Re: Current Financial Year & Previous Financial Year

vardhancse — Wed, 01 Feb 2017 06:51:18 GMT

Can Please let me know any option to get max of Financial Year

so that based on Financial Year selection bar chart should be displayed.

Current Year:

=Sum({<Month=,[Financial Year]={'FY17'}>}[Total Cost (actual)])

Previous Year:

=Sum({<Month=,[Financial Year]={'FY16'}>}[Total Cost (actual)])

Instead of static I want to make it a dynamic based on Financial Year selection

Re: Current Financial Year & Previous Financial Year

kaushiknsolanki — Wed, 01 Feb 2017 06:52:42 GMT

If Year is in 1900 and if that value is correct then it will work.

But what I have seen is if you use date function on date then sometimes it gives dates in 1900 etc format.

In that case it may give wrong values.

Please check that.

Regards,

Kaushik Solanki

Re: Current Financial Year & Previous Financial Year

vardhancse — Wed, 01 Feb 2017 06:56:44 GMT

Hi If i am selection Financial Year = FY17

Then Year will be both 2016 & 2017

I am converting calendar into Fiscal calendar and so.

Our financial year starts from Oct '16 and end at Sep 17

Re: Current Financial Year & Previous Financial Year

kaushiknsolanki — Wed, 01 Feb 2017 09:32:40 GMT

Hi If possible share sample data along with expected output.

Regards,

Kaushik Solanki

Re: Current Financial Year & Previous Financial Year

vardhancse — Wed, 01 Feb 2017 10:00:28 GMT

Hi,

For time being used the below conditions (Static):

current year:

=if([Financial Year]='FY18',Sum({$<Month=, [Financial Year]={'FY18'}>} [Total Cost (actual)]),

if([Financial Year]='FY17',Sum({$<Month=, [Financial Year]={'FY17'}>} [Total Cost (actual)]),

if([Financial Year]='FY16',Sum({$<Month=, [Financial Year]={'FY16'}>} [Total Cost (actual)]),

if([Financial Year]='FY15',Sum({$<Month=, [Financial Year]={'FY15'}>} [Total Cost (actual)])))))

Previous Year:

=if([Financial Year]='FY18',Sum({$<Month=, [Financial Year]={'FY17'}>} [Total Cost (actual)]),

if([Financial Year]='FY17',Sum({$<Month=, [Financial Year]={'FY16'}>} [Total Cost (actual)]),

if([Financial Year]='FY16',Sum({$<Month=, [Financial Year]={'FY15'}>} [Total Cost (actual)]),

if([Financial Year]='FY15',Sum({$<Month=, [Financial Year]={'FY14'}>} [Total Cost (actual)])))))

for time being issue was resolve, but looking for a dynamic kind of thing like based on Financial Year selection the months total amount should be displayed.

Re: Current Financial Year & Previous Financial Year

Anil_Babu_Samineni — Wed, 01 Feb 2017 10:12:22 GMT

Does Financial Year is a field having values are FY14,15,16,17 and then so on? Confirm me. Really better offer if you provide sample to look on it

Re: Current Financial Year & Previous Financial Year

vardhancse — Wed, 01 Feb 2017 12:16:00 GMT

Yes Financial Year is a field having data as

FY16

FY17

FY18

Re: Current Financial Year & Previous Financial Year

sdmech81 — Wed, 01 Feb 2017 12:30:11 GMT

Can you please replace that max(Year-1) with Addmonths(max(Year),-12) and check..Try put them in variable.

If not,will pass on better master calendar..

Sachin

Re: Current Financial Year & Previous Financial Year

sdmech81 — Wed, 01 Feb 2017 12:31:16 GMT

Can use Addyears()..:)