topic Re: Financial Year Set Analysis in QlikView

Financial Year Set Analysis

Anonymous — Fri, 10 Feb 2017 13:33:13 GMT

I need to return data in set analysis for this financial year only (April to March).

I have a field that shows which financial Year the data is in [Financial Year].

How do I create set analysis which returns a value only if we are in the current financial year.

e.g. Only sums data if today is within financial year 2016 (any data between April 2016 - March 2017)

Thoughts?

Thanks

Phil

Re: Financial Year Set Analysis

Gysbert_Wassenaar — Sat, 11 Feb 2017 18:21:02 GMT

Perhaps something like this: sum({<Year=,Month=, [Financial Year]={2017}>}Amount)

Re: Financial Year Set Analysis

ahaahaaha — Sun, 12 Feb 2017 07:56:31 GMT

Hi Phil,

If select not all the dates of the one value of the field [Financial Year], it may as a variant of use the "Date", for example

=Sum({<Date={">01.04.2016"}*{"<31.03.2017"}>} Volume)

Regards,

Andrey

Re: Financial Year Set Analysis

rahulpawarb — Sun, 12 Feb 2017 14:55:50 GMT

Hello Phil,

Please refer below given draft expression:

Sum({<FiscalYear={"=If(Today() >= Date#('04/01/2016', 'MM/DD/YYYY') AND Today() <= Date#('03/31/2017', 'MM/DD/YYYY'), If(Month(Today())<=3, Year(Today())-1, Year(Today())))"}>}Sales)

Hope this will be of help.

Regards!

Rahul

Re: Financial Year Set Analysis

rahulpawarb — Sun, 12 Feb 2017 15:07:58 GMT

Hello Andrey,

I like the solution; however, it would be great if we include the boundary values as well.

=Sum({<Date={">=01.04.2016"}*{"=<31.03.2017"}>} Volume)

Please correct me if I misunderstood.

Thank you!

Rahul

Re: Financial Year Set Analysis

ahaahaaha — Mon, 13 Feb 2017 07:46:49 GMT

Hi Rahul,

Yes, of course you're right. I just showed you how combine the sets.

Regards,

Andrey

Re: Financial Year Set Analysis

Anonymous — Mon, 13 Feb 2017 08:12:37 GMT

All these are good, but none of them will automatically pick up the previous year, as I will have to change the date in the script every new financial year.

e.g. I need the financial year 2016/17 to show 2015/16 data. Then when we move into 2017/18 I need it to show 2016/17 data.

In summary it always needs to show the previous year (future proofed).

Thanks in advance

Phil

Re: Financial Year Set Analysis

rahulpawarb — Mon, 13 Feb 2017 08:47:51 GMT

Hello Phil,

Please refer below given draft expression:

Sum({<FiscalYear={"=If(Today() >= MakeDate(Year(Today())-1,4,1) AND Today() <= MakeDate(Year(Today()),3,31), If(Month(Today())<=3, Year(Today())-1, Year(Today())))"}>}Sales)

In above expression,

MakeDate(Year(Today())-1,4,1) - will return lower bound/start point i.e. 04/01/2016

MakeDate(Year(Today()),3,31) - will return upper bound/end point i.e. 03/31/2017

Hope this will help.

Regards!

Rahul

Re: Financial Year Set Analysis

ahaahaaha — Mon, 13 Feb 2017 08:49:26 GMT

Hi Phil,

Why do you load the script data separately for years? Maybe load data for all years, and then make selections in the charts?

Regards,

Andrey

Re: Financial Year Set Analysis

Anonymous — Mon, 13 Feb 2017 08:56:48 GMT

My table needs to show a column for current year and another one for last year.

It would be possible to create a set analysis which I can set specifically for 2016 or 2017, but then as we move from year to year I will have to update all my scripts to change the year. As my dashboard is quite large it is likely it will miss the change over.

I hope that makes sense.

Thanks

Phil

Re: Financial Year Set Analysis

Anonymous — Mon, 13 Feb 2017 09:07:31 GMT

This works, nice one.

Tried to use the same formula for current year. Made slight amendments, but didn't work. Any thoughts? See below

Sum({<FiscalYear={"=If(Today() >= MakeDate(Year(Today())-0,4,1) AND Today() <= MakeDate(Year(Today()),3,31), If(Month(Today())<=3, Year(Today())-0, Year(Today())))"}>}Sales)

Re: Financial Year Set Analysis

Anonymous — Mon, 13 Feb 2017 09:13:33 GMT

Try This one

=Sum({$<Month=, Year=,

CALEN_DT={">=$(=MonthStart(AddMonths(Max(CALEN_DT),-11)))<$(=MonthEnd(AddMonths(Max(CALEN_DT),-0)))"}>}Value)

Regards,

Devendar

Re: Financial Year Set Analysis

ahaahaaha — Mon, 13 Feb 2017 09:17:22 GMT

Can all the same such variant

1. The script downloads all data.

2. By using the calendar wizard, each record is linked to the financial year.

3. On the chart displays the data, which are calculated for example on the basis of the current date (ie the financial year is automatically determined by calculation).

Actually approximately the same idea proposed by Rahul above.

Andrey

Re: Financial Year Set Analysis

rahulpawarb — Mon, 13 Feb 2017 10:09:02 GMT

Hello Phil,

Could you please try below given sample expression:

Sum({<FiscalYear={"=If(Today() >= If(Month(Today())<=3, MakeDate(Year(Today())-1,4,1), MakeDate(Year(Today()),4,1)) AND Today() <= If(Month(Today())<=3, MakeDate(Year(Today()),3,31), MakeDate(Year(Today())+1,3,31)), If(Month(Today())<=3, Year(Today())-1, Year(Today())))"}>}Sales)

P.S.: To reduce the length of expression you can make use of variables.

Regards!

Rahul