I have a table with sold and lost events, and their dates per company. I would like to know the number of lost events between two sold events in a given timeframe displayed on a pivot table.
company A Sold 11-15-2022
company A Lost 11-18-2022
company A Lost 12-03-2022
company A Sold 12-05-2022
Results should look like:
For 2022
Company X Number of lost events
Company A 2
Won’t a count of lost rows work, unless there is a sold event and the year ends with a lot of lost events?
For instance
company A Sold 12-05-2022
company A Lost 12-10-2022
company A Lost 12-28-2022
In that case you wouldn’t want to count those last two right?
And the beginning of the year.
Could it look like this?
ifelse({win_loss}=‘Lost’ AND {date_field}< minOver((ifelse({win_loss}=‘Sold’,{date_field},NULL),[truncDate(‘YYYY’,{date_field})],PRE_AGG),NULL,‘Count’)
This would be for the beginning of the year ^
For both it might look like this.
ifelse(({win_loss}=‘Lost’ AND {date_field}< minOver((ifelse({win_loss}=‘Sold’,{date_field},NULL),[truncDate(‘YYYY’,{date_field})],PRE_AGG)) AND (({win_loss}=‘Lost’ AND {date_field}> maxOver((ifelse({win_loss}=‘Sold’,{date_field},NULL),[truncDate(‘YYYY’,{date_field})],PRE_AGG)),NULL,‘Count’)
Then you would count this field.
Let me know if that works
Hi Max,
First of all thank you very much for taking the time to reply to my request. It seems that your answer didn’t work but It is probably because my question was not complete enough (my apologies). There could be more than 1 sold project per year. Please find additional data points below
The end goal is to have a line visual where I would be able to see the number of lost projects between sold projects. (see image attached, red bars represent sold project dates, displayed just for explaining I won’t be adding the bars). On the X axis I will have the project dates and the Y axis will be the count of lost projects in between the sold projects.
The count and display columns are what I am trying to get (not part of the dataset), I assume that with a runningsum I would be able to solve this problem. However, I am very new using QuickSight and I don’t know how to set the sum to 0 once a sold event occurs. This is where I stopped because I am not sure how to proceed:
runningSum(sum(ifelse({win_loss}=‘Lost’,1,0)),[{date_field} ASC])
The added complexity comes when if the last event is a lost project, it would continue to add the number of lost projects until a new sold one arrives.
| Company | Status | Date | Count | Display |
|---|---|---|---|---|
| Company A | Won | 12/5/2019 | 0 | |
| Company A | Lost | 12/20/2019 | 1 | 1 |
| Company A | Won | 1/4/2020 | 0 | |
| Company A | Lost | 1/19/2020 | 1 | 2 |
| Company A | Lost | 2/3/2020 | 1 | 2 |
| Company A | Won | 2/18/2020 | 0 | |
| Company A | Won | 3/4/2020 | 0 | |
| Company A | Lost | 3/19/2020 | 1 | 1 |
| Company A | Won | 4/3/2020 | 0 | |
| Company A | Won | 4/18/2020 | 0 | |
| Company A | Lost | 5/3/2020 | 1 | 3 |
| Company A | Lost | 5/18/2020 | 1 | 3 |
| Company A | Lost | 6/2/2020 | 1 | 3 |
Thank you very much for your help in advance
Can you add a partition to your runningSum field that will grab the last won status date?
runningSum(sum(ifelse({win_loss}=‘Lost’,1,0)),[{date_field} ASC],[lag({date_field},[{win_loss} DESC],1)])
Let me know if that works
I was able to make the sum work but could not make to restart from zero once a won project happens. I know I am missing a partition in the running sum, however, I don’t know how to make adjust the formula to account for it
runningSum(sum(ifelse({win_loss}=‘Lost’,1,0)),[{win_loss} ASC],[{win_loss}])
Are you saying this doesn’t work? Partitioning by the latest won date is how you can get it to start from zero.
runningSum(sum(ifelse({win_loss}=‘Lost’,1,0)),[{date_field} ASC],[lag({date_field},[{win_loss} DESC],1)])
That’s right, that formula shows the number of lost projects per their date but it doesn’t provide the running sum of the number of consecutive lost projects between sold ones.
For instance, on Sep 1,2021 the y-axis value should be 2. It would count the previous lost project and the current one.
Ok I think I have something. I don’t know if it will work exactly for you.
First make a minOver reference field. This will get all the loses and group them together when they are between a win / loss.
minvOver reference = ifelse({win_loss}= “Loss”,minOver({date_field},[{win_loss}],PRE_AGG),NULL)
Then take the running sum of and partition by that ^
runningSumOver reference = runningSum(sum(ifelse({win_loss}=‘Loss’,1,0)),[{date_field} ASC],[{minvOver reference}])
Finally, you can take the lag of it to reference on your winning date.
lag({runningSumOver reference},[{date_field} ASC],1)
Let me know if that works.
Max, on the chart on my previous post I have the date_field on the x axis so win_loss field are already grouped between win/loss events. Would I need the minOver reference formula that you suggested above then?
I followed your steps and since I’m using a chart visual it requires the partition to be on the field wells. But I only can use date_field for X axis and our formula for the Y axis.
Yes you need that in the minOver because that what will separate it when there is a new win.
For your line graph you’ll probably need to translate that minOver reference into SQL. If it’s in sql then you don’t need it in the field wells.
