How to create bar graph for "Rolling 12 months data excluding current ongoing month?

[Anonymous]

Hello All,

I am trying to create bar graph for "Rolling 12 months data excluding current ongoing month" and i have an expression set in master item but the output includes data from current month also. Below is the expression -

COUNT(

{

1<[Created On Dt]={'>=$(=Monthstart(Addmonths(Max([Created On Dt]),-12)))<=$(=Max([Created On Dt]))'}>

}

IF(ISNULL(Id),0,1)

)

P.S. - I am completely new in this Qlik sense world! Any help with simple explanation will be appreciated.

[william_fu]

So Max([Created On Dt]) will always give you the latest date in your data (which I assume is equivalent to the current month)

Using MonthEnd(Max([Created On Dt]),-1) means you're calculating up to the last day of the month before the current one.

e.g.:

Max([Created On Dt]) = 15/08/2018

MonthEnd(Max([Created On Dt]),-1) = 31/07/2018

COUNT(

{

1<[Created On Dt]={'>=$(=Monthstart(Addmonths(Max([Created On Dt]),-12)))<=$(=Monthend(Max([Created On Dt]),-1))'}>

}

IF(ISNULL(Id),0,1)

)

[zebhashmi]

why you are using count

I think if you want to make a graph your dim would be something like William explained

only({1<[Created On Dt]={'>=$(=Monthstart(Addmonths(Max([Created On Dt]),-12)))<=$(=Monthend(Max([Created On Dt]),-1))'}>}Created On Dt)

[Anonymous]

Thank you William, it worked!

But then i tried to change colors in stacked bar graph and then x-axis is showing me extra data points which is not required..

Can you please help?

[Anonymous]

Any help please?