topic Re: Year to date Set Analysis in QlikView

Year to date Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have the following set analysis for calculating Year to Date based on calendar year:

where MonthID is calculated as follows:

i have a fiscal month variable which is vFM = 6 denoting when the month the fiscal year starts. How can this be adjusted to come to a Year to Date based on the fiscal year calendar?

Regards.

Re: Year to date Set Analysis

sunny_talwar — Tue, 03 May 2016 08:04:08 GMT

May be something like this in the script

AutoNumber(Mod(Month(Date) - $(vFM), 12)+1) as FiscalMonthID

Num(Mod(Month(Date) - $(vFM), 12)+1) as FiscalMonthID

NOTE: Grabbed the logic from here

Fiscal and Standard Calendar generation

Re: Year to date Set Analysis

Anonymous — Tue, 03 May 2016 17:08:54 GMT

Many thanks Sunny

The recommendation above gives the same value as the calendar Year to Date

Regards

Re: Year to date Set Analysis

sunny_talwar — Tue, 03 May 2016 17:13:49 GMT

How about something like this:

Sum({<Year, Quarter, Month, MonthID, Date = {"$(='>=' & Date(YearStart(Max(Date), 0, 6), 'YourDateFieldFormatHere') & '<=' & Date(Max(Date), 'YourDateFieldFormatHere'))"}>} Amount)

Re: Year to date Set Analysis

Anonymous — Tue, 03 May 2016 18:15:30 GMT

Sunny the moment i enter the expression, i get a message 'Error in expression'

Re: Year to date Set Analysis

sunny_talwar — Tue, 03 May 2016 18:17:56 GMT

Can you share a screenshot? In the mean time I can check the syntax.

Re: Year to date Set Analysis

sunny_talwar — Tue, 03 May 2016 18:19:38 GMT

Sum({<Year, Quarter, Month, MonthID, Date = {"$(='>=' & Date(YearStart(Max(Date), 0, 6), 'YourDateFieldFormatHere') & '<=' & Date(Max(Date), 'YourDateFieldFormatHere'))"}>} Amount)

Did you replace YourDateFieldFormatHere with your datefield's format or did you just copy and paste the whole thing?

Re: Year to date Set Analysis

Anonymous — Tue, 03 May 2016 19:19:42 GMT

I replaced it with 'MM/DD/YYYY'

Re: Year to date Set Analysis

sunny_talwar — Tue, 03 May 2016 19:24:15 GMT

Can you share a screenshot of the error? Did you try clicking okay to see if you still see any result or not?

Re: Year to date Set Analysis

Anonymous — Tue, 03 May 2016 20:22:29 GMT

Many thanks, i had not click ok, when i do so i get the following result:

However the calculated Year to Date is 8 132 064.83

Re: Year to date Set Analysis

sunny_talwar — Tue, 03 May 2016 20:24:51 GMT

Can you check if this is giving you the right date range. Try it in a text box object

='>=' & Date(YearStart(Max(Date), 0, 6), 'MM/DD/YYYY') & '<=' & Date(Max(Date), 'MM/DD/YYYY')

Re: Year to date Set Analysis

Anonymous — Wed, 04 May 2016 05:04:59 GMT

HI Sunny

Changed your expression to the following since my fiscal year start month is 10:

This gives the correct date range as follows:

I then changed the expression for Year to Date to:

This gives the same value as the old value as follows:

Regards.

Re: Year to date Set Analysis

Anonymous — Wed, 04 May 2016 06:52:54 GMT

Hi Sunny

Ended up using flags as follows:

then using set analysis with

giving the correct result as :

How do i do the following flags:

Previous month
Previous two months
Quarter to date
Last year quarter to date
Same quarter of last year

Regards