6 Replies Latest reply: Jun 25, 2018 7:14 PM by Steve Dark RSS

    How to highlight or show only New data based on Time Frame (month)

    Joey Lake

      Hello again Qlik Community,


      I have another specific question, I'm hoping to figure out.


      I have a set of data going back to the start of 2017 by day.  I track and measure a lot of things at a MTD level though as well.  What I'm wanting to do, is create a visualization wherein it will basically Highlight or only display New Accounts based on certain parameters.


      Here's a simplified layout of my data:


      DateAdvertiser NameSpend
      4/30/2018Company A99.40


      Company B107.65
      5/1/2018Company A253.45
      5/1/2018Company B110.11
      5/1/2018Company C74.32


      So in this small excerpt, basically, lets assume that Company C is "New" in May.  We know this, because Company C doesn't have any spend in April (assuming the sample data was expanded).  Basically, I'm trying to thin of a formula that will basically compare Current Month spend and really only report back the Advertiser Name IF that spend of Current month is greater than if that Advertiser had any spend previous month.  If that advertiser had even $1.00 spend in Previous Month, I would not want to highlight that it's new, but if there was No spend in previous month, I'd like to be able to show visually, and quickly, what would be considered a New Account for the month.


      Let me know if this makes sense, and I'm happy to provide any additional detail.  Thank you in advance.

        • Re: How to highlight or show only New data based on Time Frame (month)
          Joey Lake

          I've been trying to think of ways to take


          if"Sum of Current Month" >0, "Sum of Last Month" =0, then output "Advertiser Name"


          Basically reading it as if Current Month Revenue is Greater than 0 and the Sum of Last Month = 0, output the 'Advertiser Name' Field


          No Luck so far getting the syntax right.

          • Re: How to highlight or show only New data based on Time Frame (month)
            sasi k

            Try this,

            Dimension: Advertiser Name

            Exp: Sum({<[Advertiser Name]={"=count( [Advertiser Name])=1"}>}Spend)

            If you want to retrieve only latest month related then you can use below.

            Exp: Sum({<[Advertiser Name]={"=count( [Advertiser Name])=1"}, Month={$(=max(Month) )}>}Spend)

            • Re: How to highlight or show only New data based on Time Frame (month)
              Steve Dark

              Suggest you would want to do this in the colour condition for the background.


              To work out the spend before the latest month you would need to use a TOTAL function, which totals ignoring dimensions in the chart, and set analysis to exclude the current month.  You will need a Month field in the data, as well as a date (Created in the load script with Date(MonthStart(Date), 'MMM-YY') as Month, ) to use with the Set Analysis.


              The colour condition would then be something like:


              =if(sum(TOTAL <[Advertiser Name]> {<Month={"<=$(=Date(Max(Month), 'MMM-YY')"}>}Spend) > 0, rgb(160,220,160), null())

              Happy to explain what any of the component parts of that expression are doing if it is not clear, or if it doesn't work!



                • Re: How to highlight or show only New data based on Time Frame (month)
                  Joey Lake

                  Thanks Steve,  I'm finally getting a chance to circle back to this, and I'm unable to get it to work.  I think the critical piece I may be confused on is the Month Field in the data (I do have the month in there as a MMM-YY format).  But I tried the color condition, and it's not yielding any results in the background colors. 


                  I'm attempting to do this as a Table, wherein I could ultimately sort or ignore those that aren't matching the correct expression. 


                  If you could review again, and let me know what more I can share for you, I'd greatly appreciate it.  Thanks!

                    • Re: How to highlight or show only New data based on Time Frame (month)
                      Steve Dark

                      Hi Joey,


                      There's a couple of glitches in the code I posted, it should have a less than, rather than less than or equal and there was a missing bracket in the Date dollar expansion. So, it should have been:


                      =if(sum(TOTAL <[Advertiser Name]> {<Month={"<$(=Date(Max(Month), 'MMM-YY'))"}>}Spend) > 0, rgb(160,220,160), null())


                      The thing to do is to put the constituent parts of the colour expression as measures in your table, to see if they work.


                      Change the colour and null values to 0 and 1 and add this as another expression:


                      =if(sum(TOTAL <[Advertiser Name]> {<Month={"<$(=Date(Max(Month), 'MMM-YY'))"}>}Spend) > 0, 1, 0)


                      Then add a column for spend before current month:


                      sum(TOTAL <[Advertiser Name]> {<Month={"<$(=Date(Max(Month), 'MMM-YY'))"}>}Spend)

                      And another expression for max month (this will be the same for all rows):


                      $(=Date(Max(Month), 'MMM-YY'))


                      By checking the constituent parts of the expression you should hopefully be able to work out what is going on.