topic Re: Calculating Amount between two time periods Syntax help in QlikView

Calculating Amount between two time periods Syntax help

Wed, 04 Sep 2013 19:29:45 GMT

Hi,

I have a pivot chart with Product, Contract Number and Contract End Date listed on the Y Axis (rows) and months (Jan 2013, Feb 2013 ... Dec 2014) on the X Axis (Columns). Contracted amount by month (sum(Amount)) is the expression.

So the chart looks like as shown below

Product ContractNo. ContractEndDate YearMonth Jan 2013 Feb 2013 Mar 2013 .....Dec 2014

Prod A 000001 10/31/2015 $100 $200 $50 ..... $133

Prod B 000005 4/30/2014 $50 $75 $60 ..... $20

If we assume the Closed Month = August 2013, I want to create a calculation (Total Remaining Amount) (a column next to Contract End Date) that sums up all amounts From Sep 2013 upto 10/31/2015 for Prod A and upto 4/30/2014 for Prod B

The calculation that I need help with the syntax in words is

Sum All Amounts between Sep 2013 (i.e. ClosedMonth +1) and Contract End Date

Any help will be greatly appreciated.

Thanks,

Amitesh

Re: Calculating Amount between two time periods Syntax help

jpapador — Wed, 04 Sep 2013 20:56:22 GMT

Try this as your expression:

Sum(If(YearMonth >= Month(Today()) and YearMonth <= (Month(ContractEndDate)+1), Amount))

Re: Calculating Amount between two time periods Syntax help

Thu, 05 Sep 2013 17:37:48 GMT

Hi Jpapador,

Thanks for your response. the trouble with that expression is that it compares month with a year and month combination and returns everything under the sun.

I tried something like that myself before posting the question as given below

sum(

if(Month>= (Month($(vVersionMonthEnd)) +1) AND Month(YearMonth)<=Month(ContractEndDate) ,Amount))

where vVersionMonthEnd returns the version closed date like 2013/08/31, column Month returns Jan Feb Mar...

The trouble with that expression is that when I put it in a straight chart to view values by month, values for months Jan- Aug for each year is stripped off, when I want those months to be stripped off only for the current year.

the trick here is successfully comparing year and month from the YearMonth column which has values like 2013-01, 2013-02 and so on.

That is the expression help I am looking for.