topic Set Analysis Year To Date in QlikView

Set Analysis Year To Date

Thu, 19 Nov 2009 16:12:15 GMT

This is my first atempt with SET ANALYSIS and I have used the following formula successfully to obtain year to date value of VALUEFIELD where the fiscal year starts on January 1.

sum({<YEARNOCAL={*}, PERIODNOCAL={*}, DATEFIELD = {"<=$(=date(max(DATEFIELD)))>=$(=date(max(yearstart(DATEFIELD))))"}>} VALUEFIELD)

Our customers do not always have the fiscal year starting on January 1, e.g. Period 1 could start on October 1. I want to select the YEARNOCAL and PERIODNOCAL, e.g. 2009 period 2, and I want the set analysis to select data from the first date held where PERIODNOCAL = 1 the the last date for the selected PERIODNOCAL.

I hope I have made my requirements clear. If not please let me know if anyone needs any further informaton.

Thanks

Peter

Set Analysis Year To Date

Thu, 19 Nov 2009 16:47:51 GMT

If the fiscal year does not begin on January, you need to use the yearname function to retrieve the exercice year.

For example if the year is from July to June, then Yearname(MyDate,0,7)

Where 7 is the first month of the exercice.

When I run set analysis for Year to date i Have two set analysis: The first one is month <monthdate and Exercice = yearname(MyDate,0,X)

I hope this will help.

Rgds,

Sébastien

Set Analysis Year To Date

Thu, 19 Nov 2009 18:21:44 GMT

Thanks Sébastien

This will work very well where the start date for the period is the 1st of the month. We have the scenario where periods can be 4 or 5 weeks in length rather than a calenar month, therfore the start date for period 1 if in July could be July 3rd

Can you think of an additional way?

Peter

Set Analysis Year To Date

Thu, 19 Nov 2009 18:37:52 GMT

I think you should add a field in existing table or new table. This field would be periodnum and then you could add it in your set analysis.