Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

paredlx2
New Contributor

Sum last 2 months

Hi Everyone

I have one question how I should sum the last 2 months when I have a date field - group (Year, Quarter, Month) as a dimension. even I change de view I want the calc remains. thanks in advance.

Capture.PNG

 

 

Labels (3)
4 Replies
Partner
Partner

Re: Sum last 2 months

Use Set Analysis and the field with the lowest level of detail, in this case, the date, so that the filters or range you define in your set analysis expression remain valid for any dimension that represents an aggregated level (month, year) of the filtered field (date). This post contains a pdf document (https://community.qlik.com/t5/QlikView-Documents/Set-Analysis-syntaxes-examples/ta-p/1491810) that is a good guide to learn set analysis and there are plenty of examples of how to achieve rolling periods using set analysis here in the community.

Re: Sum last 2 months

Try this expression

=If(GetCurrentField([Group]) = 'Month',
RangeSum(Above(Sum(values), 0, 2)),
RangeSum(FirstSortedValue(values, -date), FirstSortedValue(values, -date, 2)))
Highlighted
paredlx2
New Contributor

Re: Sum last 2 months

Hi Stalwar1 thanks for your response. already have a couple of days trying to implement your solution but I can't do this works. please can you help me with this.

Re: Sum last 2 months

Try this

=If(GetCurrentField([Time_DOH]) = 'Month',
RangeSum(Above(Sum(Sales), 0, 2)),
RangeSum(FirstSortedValue(Aggr(Sum(Sales), Full_date, $(='[' & GetCurrentField([Time_DOH]) & ']')), -Aggr(Full_date, Full_date, $(='[' & GetCurrentField([Time_DOH]) & ']'))),
		 FirstSortedValue(Aggr(Sum(Sales), Full_date, $(='[' & GetCurrentField([Time_DOH]) & ']')), -Aggr(Full_date, Full_date, $(='[' & GetCurrentField([Time_DOH]) & ']')), 2)))