topic Re: Difference between months on a chart in QlikView

Difference between months on a chart

Thu, 26 Jul 2012 04:39:42 GMT

I am trying to calculate the

1. difference from previous month

2. difference of each month from last year, rolling month. for example if we are in june, then each month since june of 2011.

I have a chart showing 12 months info. I am using

Dimension:

if(pd.ReportMonth<=vReportMonth And pd.ReportMonth>=Addmonths(vReportMonth,-11),Date(pd.ReportMonth,'MMM YYYY'))

Any idea or suggestion

See my sample chart:

Re: Difference between months on a chart

Thu, 26 Jul 2012 09:29:23 GMT

in my opinion... this way not:)

try to change dimension from MonthYear to Month

and change the all the expression (set the year by set analysis)

then you can make an expression to calculate diff month last year

for calculating diff to prev month - check above() function

Re: Difference between months on a chart

Thu, 26 Jul 2012 13:19:57 GMT

Pari Pari, can you please provide more info on how to change the expression (set the year by set analysis)

in my expression of % PD of NTK Premium(which is what am trying to find the difference from previous month) I am using PD NTK Premium/PD Premium which is the same as----- sum({$<c.ReportMonth=>} (pd.NTK) / sum({$c.ReportMonth=>}(pd.Submit)

My dimension is

if(pd.ReportMonth<=vReportMonth And pd.ReportMonth>=Addmonths(vReportMonth,-11),Date(pd.ReportMonth,'MMM YYYY'))

Re: Difference between months on a chart

Thu, 26 Jul 2012 13:36:36 GMT

it'l be easier if you post sample qvw file;)

Re: Difference between months on a chart

Thu, 26 Jul 2012 13:49:51 GMT

Formulas:

PD premium = total of PD Premium of all branches

PD NTK Premium= total of NTK

% PD NTK of PD Prem = NTK / PD Premium

PD NTK% Difference from Previous Month= is what am trying to
find

Ignore month Only

I’ve attached a file sample

Re: Difference between months on a chart

nstefaniuk — Thu, 26 Jul 2012 14:02:08 GMT

You can use Above if you want to use only data displayed in graph.

Else you can do that with a combination of Aggr and Above (but that's tricky).

Usually I solve this issue with a good model : http://community.qlik.com/message/229267#229267

Re: Difference between months on a chart

Thu, 26 Jul 2012 14:03:26 GMT

try this way :

Re: Difference between months on a chart

Thu, 26 Jul 2012 14:37:50 GMT

It is close. Is there not a way not not to hard code the year in. It is monthly across, so what if Ithere is october 2011 thru September 2012. where you hard coded the year for PD premium and PD NTK Premium, would not work?. Also the difference am looking for first is what is the difference of % PD NTK of PD premium from last month...

Re: Difference between months on a chart

Fri, 27 Jul 2012 14:10:29 GMT

I figured this out. I still used the dimension from above and for expression for the % difference from previous month i used :

column(4)-Before(TOTAL Column(4))

and to get difference from from rolling 12 month i.e

column(4)-First(TOTAL Column(4))