Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jlakehivewyre
Contributor III
Contributor III

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

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

4/30/2018

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.

6 Replies
jlakehivewyre
Contributor III
Contributor III
Author

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
Master
Master

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
Contributor III
Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Contributor III
Contributor III
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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