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: 
ciaran_mcgowan
Partner - Creator III
Partner - Creator III

Alternate States: Tracking Sales between selected dates

Hi all,

 

I have a application that tracks a region's top 10 sales people per month (flag created in script). An additional requirement has been added to track how any month's top 10 people can be tracked in the future. So instead of showing all sales where "FLAG.TOP_10 = 1" over n months, they want the ability to select a start date (i.e. date that determines which sales people were in the top 10 for that month) and an end date (e.g. latest month) to compare their sales to. Initial tests went well and I was able to produce a table like this (code below):

Start Date: Feb'18

End Date: Jun '18

Sales Person, Feb'18 Sales, Jun '18 Sales, Sales Movement, Still in Top 10?

John, 10.5k, 11.2k, +0.7k, Yes

Paul, 10.3k, 13.1k, +2.8k, Yes

George, 10.2k, 10.0k, -0.2k, Yes

Ringo, 9.7k, 5.2k, -4.5k, No

 

if(Only({$<Period=, Month=, Year=, [Period Date] = {'$(=Min({Alt1}[Period Date]))'}>} Flag.Top_10) = 1 AND Flag.Top_10 = 1,
[Sales Amount (€)] )
...etc.

I'm having difficulty translating this expression into monthly trend charts that shows the Start Month followed by the 5 months preceding & including the End Date. The below script shows me the Start Date and End Date but will not show the 4 months prior to the End Date. Any thoughts on where I'm going wrong?

Sum({Alt1<Flag.Top_10 = {1}, [Period Date] = {'$(=Min({Alt1}[Period Date]))'}>} [Sales Amount (€)]) // Start Month
+
// End Month & Preceding 4 months
Sum( Aggr( if(Only({$<Period=, Month=, Year=, [Period Date] = {'$(=Min({Alt1}[Period Date]))'}>} Flag.Top_10) = 1 AND Flag.Top_10 = 1, Sum({$<[Period Date] = {">=$(=Max(AddMonths([Period Date],-5)))<=$(=Max([Period Date]))"}>} [Sales Amount (€)]) ) , Sales_Person_ID))

 

 

Labels (2)
3 Replies
sunny_talwar

Would you be able to share a sample to show what you have? And also point out what exactly are you looking to get from the sample app provided?

ciaran_mcgowan
Partner - Creator III
Partner - Creator III
Author

Hi Sunny,

 

I'm not able to share the app as it contains confidential client data. What I am looking to get is the ability for a user to select 2 dates and:

1. Show the Top 10 Sales Amounts for the first date selected and

2. Show the Sales Amounts for all dates between both dates selected, but only for the people flagged in the Top 10 on the first date selected.

I've tried using an As-Of table in the script but did not have any luck so I'm hoping to achieve this in chart calculations. The script I posted above will display what I need for both dates selected, but I cannot get it to display for all dates in-between the two when building a trend chart.

sunny_talwar

Is it possible to may be provide some mocked up data?