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

[jlakehivewyre]

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:

Date	Advertiser Name	Spend
4/30/2018	Company A	99.40
4/30/2018	Company B	107.65
5/1/2018	Company A	253.45
5/1/2018	Company B	110.11
5/1/2018	Company C	74.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.

[jlakehivewyre]

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.

[sasikanth]

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)

[jlakehivewyre]

Thank you for that - though I'm not certain if you are meaning to use the Dimension of Advertiser Name and then plug the Expression into the same Field?  Or does the Expression need to go into the Measure?  I have tried both combination and not able to get my desired output.

[stevedark]

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!

Steve

[jlakehivewyre]

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!

[stevedark]

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.

Steve