4 Replies Latest reply: Jun 27, 2018 7:55 AM by Gulshan Rohilla RSS

    Before Function in Pivot table

    Gulshan Rohilla

      Hello everyone,

       

      I have a pivot table in Qlik Sense in which two measures were there, first is Sales and Second one is upto Sales.

      1.) Sales :- Sales is given(already calculated)

      2.) Upto Sales :- Derived measure from sales like if i have sales from Jan to Dec then in upto sales measure i want in jan month jan sales shows, in feb = jan+feb, in mar=jan+feb+mar, in apr=jan+feb+mar+apr.......................and in dec=jan+feb+mar+apr+..................+dec (i.e. upto that month)

      I have written some expression but i am facing one issue i.e.

      Jan = Jan

      Feb = Jan + Feb

      Mar = Feb +Mar

      ...

      ...

      ...

      ...

      Dec = Nov + Dec

      i.e. only previous month plus current month.

      below is my attached expression:-

      IF($(eSalesPartyWiseYTD)<>NULL() AND BEFORE($(eSalesPartyWiseYTD))<>NULL(),RANGESUM($(eSalesPartyWiseYTD),BEFORE($(eSalesPartyWiseYTD))),

      IF((ISNULL(BEFORE($(eSalesPartyWiseYTD))) OR BEFORE($(eSalesPartyWiseYTD))='-') AND $(eSalesPartyWiseYTD)<>NULL(),$(eSalesPartyWiseYTD),

            IF(ISNULL($(eSalesPartyWiseYTD)) AND BEFORE($(eSalesPartyWiseYTD))<>NULL(),BEFORE($(eSalesPartyWiseYTD)),

                IF(ISNULL($(eSalesPartyWiseYTD)) AND ISNULL(BEFORE($(eSalesPartyWiseYTD))),'0'))))


      Below is the attached screenshot.

      Kindly help me

      before pivot table.PNG

      before2.PNG