topic Re: Trouble with Addmonths formula in QlikView

Trouble with Addmonths formula

Thu, 03 Dec 2015 19:24:52 GMT

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

Re: Trouble with Addmonths formula

JonnyPoole — Thu, 03 Dec 2015 19:32:37 GMT

Try this. Tried to space out the syntax to follow better

Sum (

{$<

[Bill Date]=

{"=<$(

=addmonths(MonthStart(Today()),-1,0))

)

[Bill FC Value])

Re: Trouble with Addmonths formula

marcus_sommer — Thu, 03 Dec 2015 19:34:29 GMT

The syntax isn't quite correct, try it this way:

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

I think also this will be helpful: Calculating rolling n-period totals, averages or other aggregations

- Marcus

Re: Trouble with Addmonths formula

nico_ilog — Thu, 03 Dec 2015 21:29:44 GMT

Hi Craig,

Last 12 months rolling as in MAT "Moving Annual Total".

I had days of sitting with that, but figured it out this way.

The Preparation:

I have 3 variables:

1. vMaxDate value is:=Date(max(Bill Date))

2. vMATTYEnd = =Date(vMaxDate) |--> This vMaxDate is set above in #1
3. vMATTYStart = =Date(MakeDate(Year(addmonths(vMATTYEnd,-11)),Month(addmonths(vMATTYEnd,-11)),1))

The set Analysis

SUM({$<SH_TransactionDate={'>=$(vMATTYStart)=$(vMATTYEnd)'}>} [BILL FC Value])

What you achieve with this is that you can dynamically say from when the MAT should run (Max Date). So its not hard coded to the current month. I've created the couterparts to create the ability to compare the rolling 12 months TY against the same months LY. Its works lke a charm.

The Pivot Chart:

Dimension1: Whatever the main dim is. Cost center, Area, Town, Client etc.

Dimension2: Simply select the Month-Year dimension I've added the script -->(date(monthstart(TempDate), 'MMM-YYYY') as Sales_MonthYear) or whatever other date dimensions you're using as your dimension. MAKE SURE YOU DRAG YOUR DATE DIMENSION FROM ITS LOCATION (Left) TO THE TOP OF THE PIVOT TO SPAN OUT THE MONTHS like the attached item B it makes it easier to read when you start adding more dimensions.

Measurement: SUM({$<SH_TransactionDate={'>=$(vMATTYStart)=$(vMATTYEnd)'}>} [BILL FC Value])

Let me know if it helps / works.

Regard,

Nico

Attachment A.

Attachment B:

Re: Trouble with Addmonths formula

Thu, 03 Dec 2015 22:22:07 GMT

I'm still not there on the formula:

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

I also thought maybe it was a date format issue so I tried

Sum (

{$<

[Bill Date]=

{"<$(

=date(addmonths(MonthStart(Today()),-1,0),

'MM/DD/YYYY')

[Bill FC Value])

Finally, I'm not looking to calculate a moving average but sum of sales each month. I'm not sure if that makes a difference in the formula or not.

I appreciate everyone's help

Re: Trouble with Addmonths formula

JonnyPoole — Fri, 04 Dec 2015 01:22:30 GMT

Craig - is your [Bill Date] wrapped in date() function during your load. This will ensure equivalency for the set analysis. addmonths() returns date() format you should be fine there without an extra date() within the set modifier search mask.

load

[Bill Date]

load

date([Bill Date]) as [Bill Date]

I would also suggest (if you make the above change to no avail) that you try the syntax i posted. It includes an preceding '=' sign to the search mask {"=< etc...

in this situation its not equal to or less than its... the search mask is equal to: <$(=<expression>) etc...

Re: Trouble with Addmonths formula

tamilarasu — Fri, 04 Dec 2015 04:59:05 GMT

Hi Craig,

Try like below,

=Sum({$<[Bill Date]={"<$(=Date(Date#(addmonths(MonthStart(Today()),-1,0),'MM-DD-YYYY'),'DD/MM/YYYY'))"}>}[Bill FC Value])

I would suggest you to create a variable like

Name: vPrevMonth

Definition: =Date(Date#(addmonths(MonthStart(Today()),-1,0),'MM-DD-YYYY'),'DD/MM/YYYY')

Then, use expression as

= Sum({$<[Bill Date]={"<$(vPrevMonth)"}>}[Bill FC Value])

Re: Trouble with Addmonths formula

Kushal_Chawda — Fri, 04 Dec 2015 05:45:17 GMT

For calculating rolling period best way to do it is to create the flags in script

see the below link

Rolling N months

Re: Trouble with Addmonths formula

jagan — Fri, 04 Dec 2015 05:59:01 GMT

Hi Craig,

Check this link hope it helps you.

Set Analysis for certain Point in Time

Regards,

jagan.

Re: Trouble with Addmonths formula

Fri, 04 Dec 2015 23:05:57 GMT

I ended up with this formula for each month:

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

It works if I don't have a date selection (ex 2015 12) at the top.

However, this is going to be part of a dashboard in which my boss will select the year and month. Is there a replacement for $ that says "Ignore the Selection at the top"? I tried deleting the $, replacing with 1, and replacing with {1-$} but haven't any luck.

Thank you

Re: Trouble with Addmonths formula

JonnyPoole — Sat, 05 Dec 2015 01:57:36 GMT

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] )

Re: Trouble with Addmonths formula

jagan — Mon, 07 Dec 2015 06:33:18 GMT

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.

Re: Trouble with Addmonths formula

Tue, 08 Dec 2015 14:46:35 GMT

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!

Re: Trouble with Addmonths formula

nico_ilog — Tue, 08 Dec 2015 17:33:36 GMT

Hi Craig,

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

Nico

Re: Trouble with Addmonths formula

Tue, 08 Dec 2015 22:09:07 GMT

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

Re: Trouble with Addmonths formula

jagan — Wed, 09 Dec 2015 01:06:30 GMT

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.

Re: Trouble with Addmonths formula

nico_ilog — Wed, 09 Dec 2015 07:39:39 GMT

Craig,

Please share an example of your date format?

Sincerely,

Nico Manro Oberholzer

+264 81 347 3090

Re: Trouble with Addmonths formula

Wed, 09 Dec 2015 16:01:17 GMT

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

Re: Trouble with Addmonths formula

nico_ilog — Fri, 11 Dec 2015 07:48:38 GMT

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

Re: Trouble with Addmonths formula

Fri, 11 Dec 2015 16:01:05 GMT

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

Thank you