Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Rolling 3 months in a chart

I wanted to show rolling 3 months in a bar chart.

Dimension should be Claim_Month

Expression should be Sum(Claim_Amount) for rolling 3 months.

If I select current month(Aug), then the chart should show Jun, Jul, Aug in the dimension and in the expression Sum(Claim_Amount) for these three months.

I have a date field Claim_Date, Month Field Claim_Month with the below inputs

25-06-2016                                                     Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep

26-06-2016

27-06-2016

28-06-2016

29-06-2016

30-06-2016

01-07-2016

02-07-2016

03-07-2016

04-07-2016

05-07-2016

06-07-2016

I tried using this expression but it is throwing error

Sum({<Claim_Year=, Quarter=, Claim_Month={‘>=$(=MonthStart(Max(Claim_Date), -3))<=$(=Date(Max(Claim_Date)))’}>} ClaimAmount )

Please help friends. This is urgent

Tags (1)
6 Replies

Re: Rolling 3 months in a chart

Hi,

Try this.

Sum({<Claim_Year=, Quarter=, Claim_Month={‘>=$(=addmonths(MonthStart(Max(Claim_Date)), -3))<=$(=Date(Max(Claim_Date)))’}>} ClaimAmount )

Regards,

Kaushik Solanki

Not applicable

Re: Rolling 3 months in a chart

 

Syntax error.

 

I want the dimension to be shown only as 3 months.

Ex: Jan Feb March to be show when I click March. Apr May June to be shown when I click June.

 

Not applicable

Re: Rolling 3 months in a chart

Syntax error.

I want the dimension to be shown only as 3 months. Ex: Jan Feb March to be show when I click March. Apr May June to be shown when I click June.

Re: Rolling 3 months in a chart

Hi,

Expression seems Ok to me.

Can you do one thing, can you put this expression in straight table and keep the expression label blank.

Now once it is available on sheet, take your cursor at Expression label and take screenshot of that and share with us.

Regards,

Kaushik Solanki

galax_allu
Valued Contributor

Re: Rolling 3 months in a chart

Hi

you can try with rangesum

RANGESUM(ABOVE(SUM(ClaimAmount),0,3))


or





Dimension:


Claim_Month   (should be in MMM-YYYY format )




Expression:

=SUM({<Claim_Month ={">=$(=Date#(Max(Claim_Month )-3,'MMM-YYYY'))<=$(=Date#(Max(Claim_Month ),'MMM-YYYY'))"},Claim_Year=, Quarter=, Claim_Date= >} [ClaimAmount])



//Edited







Not applicable

Re: Rolling 3 months in a chart

I have data from 01-09-2016 to 31-12-2016

Community Browser