topic Re: Sum last 2 months in App Development

Sum last 2 months

paredlx2 — Sat, 16 Nov 2024 21:53:54 GMT

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.

Re: Sum last 2 months

Carlos_Reyes — Wed, 14 Nov 2018 13:01:57 GMT

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

sunny_talwar — Wed, 14 Nov 2018 13:04:33 GMT

Try this expression

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

Re: Sum last 2 months

paredlx2 — Fri, 30 Nov 2018 02:08:35 GMT

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

sunny_talwar — Fri, 30 Nov 2018 12:10:53 GMT

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