1 Reply Latest reply: Oct 23, 2018 2:56 PM by Subash Lamichhane RSS

    Set Analysis Previous month sales

    Subash Lamichhane

      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.

      NoSelection.PNG

       

      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.

      withmonthSelection.PNG

       

      Select two months Prior Month populates.

      twomonthsselected.PNG

      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

        • Re: Set Analysis Previous month sales
          Subash Lamichhane

          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)