Set Analysis sum of amounts as at Year and Month Selected

[#1]

I hope someone can help me with this one as i am quite new to qlikview and slowly teaching myself how to make it work for me.

Currently I have a scenario where I am just unable to get the required answers.  What I want to do is click on Fiscal Year, Then click a specific month.  The formula or calculation of the values should be something like this:

Sum (All Transactions with a date = or smaller than the month of the year selected.

I currently have this formula but it only works on Fiscal Year.  As soon as I click on an applicable month it will only calculate for that specific month in each of the years smaller or = to the vYTDYear.  I should calculated for all months/dates smaller than the Year-Month selection.

Sum({<[Fiscal Year]={"<=$(vYTDYear)"} >} [Amount])

Your help will really be appreciated

I attach my current model

[CELAMBARASAN]

Hi,

You should go with date field.

Sum({<[Fiscal Year]=,[Fiscal month]=, [Fiscal Date]={">=$(=YearStart(Max([Fiscal Date])))<=$(=Date(Max([Fiscal Date])))"}>})

hope it helps

[#3]

Thanks for the reply but its still not working.  Replaced Fiscal Month with Month and Fiscal date with DDate as in my dataset

[#4]

This is what my formula looks like at the moment

if([Exec P&L Level] = 's','',Sum({<[Fiscal Year]=,Month=,DDate={">=$(=YearStart(Max(DDate)))<=$(=Date(Max(DDate)))"}>}))

[Anonymous]

Carel,

I was unable to open your download app, and I'm not sure if you meant to do this, but you are missing the field to Sum() on. For example, update your expression to:

if([Exec P&L Level] = 's','',Sum({<[Fiscal Year]=,Month=,DDate={">=$(=YearStart(Max(DDate)))<=$(=Date(Max(DDate)))"}>} Amount))

If this does not work, check to make sure that your date field (DDate) is in standard format, i.e. the format matches your system date settings - check it in your the initial SET statements in your load script (SET DateFormat).

I might also suggest that you incorporate the IF() logic into your Set Analysis expression to make it clearer. Based on your existing expression, it looks like you only want to sum amounts that ARE NOT [Exec P&L Level] = 's'. You can accomplish this in Set Analysis by using the following syntax:

Sum({<[Exec P&L Level]-={'s'},[Fiscal Year]=,Month=,DDate={">=$(=YearStart(Max(DDate)))<=$(=Date(Max(DDate)))"}>} Amount))

Note the -= after [Exec P&L Level]. This specifies that the value 's' should be excluded from your current selected/possible values for [Exec P&L Level]. Be aware that this assumes there are no NULL values in [Exec P&L Level].

Hope this helps,

Phil

[CELAMBARASAN]

If you have used different format other than default

if([Exec P&L Level] = 's','',Sum({<[Fiscal Year]=,Month=,DDate={">=$(=Date(YearStart(Max(DDate)),'DD/MM/YYYY')<=$(=Date(Max(DDate), 'DD/MM/YYYY'))"}>}))

Mention that in the bold part

[#7]

Phil,

Thank you its starting to work now but I seem to have another problem now. 

When I select a month it will only return the value of the months in the selected and previous years smaller than the selected month.  In other words this example is returned:

My dataset Runs from Jul 2009 to Aug 2013:

Selections made:

Fiscal Year = 2012

Month = Mar

Returns only the Sum of transactions with DDates in the following Months:

Jan 2012

Feb 2012

Mar 2012

Jan 2011

Feb 2011

Mar 2011

Jan 2010

Feb 2010

Mar 2010

What I want the calculation to do is to return the Sum of all transactions = or Smaller than 31 Mar 2012.

So basically return the SUM of (Jul 2009 to Mar 2012).  It should always start with the MIN Date and Max Date should be as per selections.

When only Fiscal year is clicked MAX Month must be MAX month in the selected year in that year.

[#8]

I think I solved this one, but dont think its optimal yet:

This is what I have for a previous year (Date-365+1)

if([Exec P&L Level] = 's','',Sum({<[Fiscal Year]=,Month=,Date={"<=$(=Date(Max([Date]-365+1)))"}>}Amount))

How can I improve the -365 part, what I want to say it basically Date minus 1 year+ 1day

[Gysbert_Wassenaar]

Try: <=$(=addyears(Max([Date])+1,-1))

[#10]

Awesome, works like a bomb!  Thanks a lot