5 Replies Latest reply: Aug 19, 2016 10:25 AM by Ian Sutherland RSS

    How can I get revenue for prior period in Qlik Sense?

    Ian Sutherland

      I want a listing of accounts that have at least one transaction in the current period showing the AdRevenue for the current period and a prior period.

      The Current and Prior periods are select by the user from dimensions/table.

       

      The data has three fields of importance:  Clientname, TranDate and AdRevenue.

      There are other fields that are not important to the goal or to the issue, except to note that they can be used to filter the data displayed and when so used such filters must be obeyed.

       

      I really want to use TranDate to define the current Period Year(TranDate), then Month(TranDate) and lastly Day(TranDate).

      Or TranDate.Cal.year, TranDate.Cal.Month, TranDate.Cal.Day. Both work and I do not see an advantage of one over the other.

       

      This makes selecting in the current period both filter the list of qualifing accounts and allows Sum(AdRevenue) to be the AdRevnue for the curernt period.

      I have defined a date island PriorDate as

      LET ThisYear=2016;
      LET LastYear=2010;

      Prior:
      LOAD
      date( Date#('$(FirstDateLastYear)') + rowno() - 1) AS PriorDate,
      day(  Date#('$(FirstDateLastYear)') + rowno()- 1) AS PriorDay,
      year( Date#('$(FirstDateLastYear)') + rowno() - 1) AS PriorYear,
      month(  Date#('$(FirstDateLastYear)') + rowno() - 1) AS PriorMonth
      AUTOGENERATE
      LastDateThisYear - FirstDateLastYear + 1;


      I also define a calendar as
      Calendar:
      DECLARE FIELD DEFINITION TAGGED '$date'
         Parameters
            first_month_of_year = 1,
            now_is = '1/1/2016'
         Fields
            Year($1) As Year Tagged '$year',
            Month($1) as Month Tagged '$month',
            Date($1) as Date Tagged ('$date', '$day'),
            Day($1) as Day tagged '$day',
            Week($1) as Week Tagged '$week',
            Weekday($1) as Weekday Tagged '$weekday',
            DayNumberOfYear($1, first_month_of_year) as DayNumberOfYear Tagged ('$numeric');

      Cal:
      DECLARE FIELD DEFINITION USING Calendar WITH first_month_of_year=1, now_is = now;

      DERIVE FIELDS FROM FIELDS FilterDate, ProjectedCloseDate, TranDate USING Cal;

       


      I have defined some measures as
      Revenue Current Period:   Sum(AdRevenue)

      Revenue Last Period:   Sum( {< TranDate.Cal.Date={">=$(=date(Min(PriorDate)))<=$(=date(Max(PriorDate)))"} >} AdRevenue)


      On the Sheet I have a table that has
      Clientname, Revenue Current Period, Revenue Last Period.
      This shows the name, the revenue of the current period and zero in most cases.

      To say that another way, revenue last period is zero in most cases, it is not zero when the "Prior Period" is a subset of the current period.

      I have tried Sum( {< TranDate={">=$(=date(Min(PriorDate)))<=$(=date(Max(PriorDate)))"} >} AdRevenue), which I expect to work but this always returns zero.

      The simplest definition of Revenue Last Period (which the documentation says to avoid for performance reasons) is
      Sum({<TranDate=PriorDate>} AdRevenue)
      This always returns zero always


      I have tried many things (way to many to list), the most successful is to define the Current Period based on its own date island (similar to Prior above).
      Then defining "Revenue Current Period" similar to revenue "Revenue Last Period".

      Now I can select current and prior dates and the table updates as I want it but all accounts are shown not just those that have a transaction this year.
      If I add a dimension based on TranDate and select a range of dates that covers both the current and prior periods things look okay but some accounts are listed that should not be (such as when Current is Jan 2016 and Prior is Dec2015, the only way to cover these two date ranges is to have all of 2015 and 2016; also accounts that have no transactions in the current period but do have transactions in the prior are shown and should not be).


      If I define Revenue Last Period  as  Sum( {1< TranDate={">=$(=date(Min(PriorDate)))<=$(=date(Max(PriorDate)))"} >} AdRevenue)
      Then I get the result I want except this will ignore any other selections made and I want them to be obeyed.

       

      I have made simplified set of data that has the data needed (Clientname, TranDate, AdRevenue) and Account, SPCode and Product_Code and has 200 or so rows.

        • Re: How can I get revenue for prior period in Qlik Sense?
          John Bosko

          Ian,

           

          To make sure I understand correctly, you want to calculate a "current" sum of AdRevenue based on the TranDate and a "prior" sum of AdRevenue based on a date in a data island.  Is this correct?

            • Re: How can I get revenue for prior period in Qlik Sense?
              Ian Sutherland

              Hi John,

               

                              Yes.

               

                              The table would then display the client name of all clients that have a TranDate in “current”, the sum of AdRevnue in “current” and sum of AdRevenue in “prior”.

               

                              Prior is the selected “date island” dates and would not overlap the dates in “current”.

                              Often “current” would be the last full month on the calendar and “prior” would be the month before. But it does not have to be so.

                • Re: How can I get revenue for prior period in Qlik Sense?
                  Ian Sutherland

                  One other point.

                  If I use a "date island" to set "current" then the results have any client that has a transaction that is dated in either "current" or "prior", which is not really what is wanted.

                  However if I then add another column to the table as a measure such as the count of Products (count of product bought in the "current" period), then all accounts/clientnames get added to the table rows. It is taking that to be count of products over all dates.

                  Yes I can change to use a count of products filtered by the "current" similar to how the sum of AdRevenue is done.

                  I would have to do that for all other measures that are added.

              • Re: How can I get revenue for prior period in Qlik Sense?
                Ian Sutherland

                An update:

                I discovered that my focus was in the wrong place, I thought the problem was in how the measure for revenue in the prior period was working but in fact that is not the case.

                 

                The issue really is the way I select the qualifying dates.

                If I build the "current" dimension based on TranDate.Cal.Year, then TranDate.Cal.Month and lastly TranDate.Cal.Day the problem of "prior" having to be a sub sets of the "current" dates happens.

                building "current" as Year(TranDate)" then Month(TranDate) and lastly Day(TranDate) also has the problem.

                 

                If I build the dimension without any hierarchy and so it just show a list of dates the problem goes away,

                I.e. with just dates the "prior" dates do not have to be within the selected dates that make "current".

                 

                However, selecting dates that way is at best messy.

                 

                Does anyone know of another way to build the hierarchy of TranDate? especially one that works with the "Revenue Last Period" defined as  Sum( {< TranDate.Cal.Date={">=$(=date(Min(PriorDate)))<=$(=date(Max(PriorDate)))"} >} AdRevenue) or Sum( {< TranDate={">=$(=date(Min(PriorDate)))<=$(=date(Max(PriorDate)))"} >} AdRevenue).

                  • Re: How can I get revenue for prior period in Qlik Sense?
                    Ian Sutherland

                    I have now figured out the answer to this.

                    In the data load I use Year(TranDate) as CurrentYear, Month(TranDate) as CurrentMonth etc. and then build the CurrentPeriod dimension from these as a drill through dimension.

                     

                    The calculation of prior period revenue is then

                    sum( {<CurrentYear=,CurrentMonth=,CurrentDay=,TranDate={">=$(=date(Min(PriorDate), 'YYYY-MM-DD'))<=$(=date(Max(PriorDate), 'YYYY-MM-DD'))"}>} AdRevenue)

                     

                    CurrentPeriod revenue is just Sum(AdRevenue).

                     

                    Now all of the other dimension/filters show grey to reflect selection of the current period (with the two island method it they did not reflect date selection).