Sum last 2 months

[paredlx2]

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.

[Carlos_Reyes]

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.

[sunny_talwar]

Try this expression

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

[paredlx2]

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.

[sunny_talwar]

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)))