Set Analysis Previous month sales

[Anonymous]

Hi Guys,

This is my test, playing around with Qlik, trying to decide if this is a BI tool I would recommend.

But I am so stumped and seeking help on getting the previous month sum of sales.I have a barchart that shows the yearly sales broken down by month.I have a table below that shows sum of current month sales and prior month sales. When I do not not select any month in barchart the sum of prior month sales works but when I select a month in barchart I get 0 for sum of prior month sales. I have no issue with Current month sales, works either month in  barchart is selected or not.

With no Month Selected in Barchart. Current month October sum of sales and September sum of sales populating.

With Month Selected in Barchart. I selected October. The current month sum of sale work fine but for prior month sum of sales I get 0. I do not understand whats going on. Strange if i select two month october and september I get prior month populated.

Selected October in barchart.

Select two months Prior Month populates.

Here is the expression I have used to get sum of current and prior month sales.

Current month:

sum(

{$<

[link_table.record_date.autoCalendar.Year]=,

[link_table.record_date.autoCalendar.Date]={'>$(=MonthStart(Max([link_table.record_date.autoCalendar.Date]), -1))<=$(=Date(Max([link_table.record_date.autoCalendar.Date])))'}

>}sales)

Previous Month:

Sum({<[link_table.record_date.autoCalendar.Year]=,

[link_table.record_date.autoCalendar.Month]=,

[link_table.record_date.autoCalendar.Date]={">=$(=monthstart(addmonths(max([link_table.record_date.autoCalendar.Date]),-1)))<$(=monthstart(max([link_table.record_date.autoCalendar.Date])))"}

>}sales)

Date populates as below

=monthstart(addmonths(max([link_table.record_date.autoCalendar.Date]),-1))=9/1/2018

Can you guys point me to the right direction? I need the previous month sum of sales populated when one month is selected in barchart.  Thank you...

Message was edited by: Subash Lamichhane

[Anonymous · Accepted solution]

I have found the solution and incase if anyone needs in future. Below is what I did

Sum({1<[link_table.record_date.autoCalendar.Year]=,

[link_table.record_date.autoCalendar.Month]=,

[link_table.record_date.autoCalendar.Date]={">=$(=monthstart(addmonths(max([link_table.record_date.autoCalendar.Date]),-1)))<$(=monthstart(max([link_table.record_date.autoCalendar.Date])))"}

>}sales)