Trouble with Addmonths formula

[#1]

Sum ({$<[Bill Date]={"$(< (addmonths(MonthStart(Today()),-1,0)))"}>}[Bill FC Value])

My goal is to create a bar chart by month for each of the last twelve months on a rolling basis.  I understand from a previous posting the way to do that is to have an expression for each month using the addmonths formula and step back -1, -2, -3...-12 months.

Please help with the formula, or if there's a better way to accomplish the same results, please let me know.

Thank you!

My data looks like:

Bill Date	Bill FC Value
11/30/2015	4353.95
11/30/2015	4603.05
11/30/2015	6960.26
11/30/2015	22772.74
11/25/2015	551.57
11/25/2015	735.43
11/25/2015	8588.16
11/25/2015	11954.88
11/25/2015	18066.96
11/25/2015	55401.6
11/25/2015	83349
11/23/2015	6839.17
11/19/2015	1913.83
11/19/2015	3435.1
11/19/2015	3500

[JonnyPoole]

YYou can cancel out 1 or more field selections using the following.  This will cancel any selection for year or month field

Sum({<Year=,Month=,[Bill Date]={">=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))"}>} [Bill FC Value] )

[jagan]

You can ignore the selections by giving respective fieldnames in the expression like below

Sum({<Month=, Year=, Quarter=, [Bill Date]={">=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))"}>} [Bill FC Value] )

Give all the date related fields in the expression to ignore in the chart.

Regards,

Jagan.

[#4]

My apologies - I must be dense.  I have tried several different versions of your formula and still haven't be able to override the selection:

Sum({<Month=,Day=,Year=, [Bill Date]={">=$(=MonthStart(Today(), -2))<=$(=MonthEnd(Today(), -2))"}>} [Bill FC Value] ) 

Sum({<Year=,Month=,Day=, [Bill Date]={">=$(=MonthStart(Today(), -2))<=$(=MonthEnd(Today(), -2))"}>} [Bill FC Value] ) 

Sum({<Month=, Year=, Quarter=, [Bill Date]={">=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))"}>} [Bill FC Value] )

Sum({<Month([Bill Date])=, Year([Bill Date])=, Quarter([Bill Date)]=, [Bill Date]={">=$(=MonthStart(Today(), -1))<=$(=MonthEnd(Today(), -1))"}>} [Bill FC Value] )

All four versions have failed.

What am I missing?

Thank you!

[nico_ilog]

Hi Craig,

Please share your qvw / qvf so we can have a look ourselves.

Nico

[#6]

Hello Nico -

Unfortunately we don't know how to send a qvw or qvf file.  We are very new to Qlikview.

Additionally, I'm concerned how much data I would be sending over the Internet.  We're a private company, and as you know owners of private companies are very sensitive about data.  Is there a way to send only the chart and not all the data?

Thank you

[jagan]

Hi Craig,

Click Reply link and on the Top Right corner click Use Advanced Editor link and click Attach link to attach the sample file.

Hope this helps you.

Regards,

Jagan.

[nico_ilog]

Craig,

Please share an example of your date format?

Sincerely,

Nico Manro Oberholzer

+264 81 347 3090

[#9]

Bill Date	Bill FC Value
12/07/2015	403.65
12/07/2015	5599.8
12/07/2015	9072.07
12/07/2015	9219.3
12/07/2015	11422.32
12/07/2015	30842.7

Here is the data as extracted into excel without formatting

[nico_ilog]

Hi Craig,

Honestly, The formulas provided should be working.

Please remove the sensitive info from the load script (Client names etc) and load only the Bill Dates | Amount with multiple months and share the qvd as explained by jagan above.

Nico

[#11]

Are these the files you are looking for?  They came out of our live .qvw folder.

Thank you