New Topic

Community Topic Question Chart Builder

Simon Karumbi

Agent

Assigned

Category Chart Builder

Product Version 7.4

Target per week/Month

Dylan Cooke-Tonnesen shared this question ago

Answered

I am trying to create a report that compares actuals to targets for sales activities. The target is a calculated field which is equal to 5 - so I just inputted 5 into that calculated filed. (Its a target of 5 per week)

I need this calculated field (Target) to change depending on the number of weeks I select. for example the default time period is This Business Week - Target = 5 but if I select last 3 weeks then the target should be 15

I am only using one date field with the operand = between. So a DateDiff Function doesn't seem to work.

I also tried adding a custom function but it doesn't work.

I am also working on another report that has a monthly target (this target field is already in the view and is not a calculated field) and I'm experiencing the same issue as above.

Is there anything I can do to get the target to work properly depending on the date range selected?

Thanks

Dylan

Files:

The same question

Comments (1)

Simon Karumbi ● 24 days ago

Hi Dylan,

Thank you for your question.

What I think you might be looking for is a Parameter. In the View, create 'Start Date' and 'End Date' Date Parameters. The create a 'Days Between' Calculated Field that performs a function, taking in the Start Date and End Date Parameters. Then, use Start Date and End Date as Filters in the Report you are creating, and Create a Calculated Field that uses 'Time Between' * 5 / 7 (for a week). This will allow you to filter by a Date Range and perform a dynamic calculation for a KPI. You could even create a Numeric Parameter for the '5' value, that will allow you to change the weekly target for some 'what if' analysis.

Let me know if this is what you are looking for.

Kind regards,

Simon

Reply URL

Dylan Cooke-Tonnesen ● 24 days ago

Hi Simon

Thanks for your reply.

The only problem is that they way my developers created the view it doesn't give me the option to add a parameter.

I managed to find a similar view that allows me to create parameters and your solution works but I can't link my activity date to the Start Date and End Date Parameter - so when I change the date filter the the Target changes but the actual completed activities done, doesn't change.

URL

Simon Karumbi ● 23 days ago

Hi Dylan,

I'm guessing that the View that the Developers have created is an SQL View? Or could you clarify what you mean when you say that you don't have the ability to create a Parameter?

Regarding the activity date Parameter, could you please send me a screenshot of the Report Building stage so I can see how you've set it up? The Start Date and End Date Parameter can be used with a Filter, by linking them through Advanced Filtering like in the image below. The top two values Start Date and End Date are default values.

/0da479fc7b9ad493ce0cf66df1cdfda0

Unfortunately, I don't know if what you are trying to achieve is possible without Parameters as what you are asking for is dynamic input. There might be a messy workaround where you can create an append subquery to join a column of new 'target' values, however, I'm not sure if this will work depending on the granularity of your Date data, and it may not be consistent across different reports.

Kind regards,

Simon

URL

Dylan Cooke-Tonnesen ● 23 days ago

Hi Simon

Yes, I believe it is a SQL view.

I tried linking the filter via the advanced filter setup - but the parameters are not coming up.

I have attached a screenshot of the report building stage as well as the advanced filter setting.

Kind Regards,

Dylan

Files:

URL

Simon Karumbi ● 22 days ago

Hi Dylan,

This might be easier to look over during a screen share. Would you be available today after 10 AM AEDT?

Kind regards,

Simon

URL

Dylan Cooke-Tonnesen ● 22 days ago

Hi Simon

I live in South Africa - so our time difference is quite big.

Kind Regards

Dylan

URL

Simon Karumbi ● 18 days ago

Hi Dylan,

If you would prefer, I can have one of my colleagues from the U.K. organise a screen share with you, that should be more convenient with the timezone.

Just quickly, have you scrolled down to see if there are more filters? There is no reason that you shouldn't be able to select a the Start Date Parameter from the Advanced Filtering options unless the Parameter value is the wrong Data Type (i.e. trying to filter a Date Value with a text value). Maybe try to save the Parameters in a separate field folder called 'Parameters' for clarities sake, there might be something going on with 'Start Date' also being the name of the Date Field you are creating as a Filter.

Kind regards,

Simon

URL

Dylan Cooke-Tonnesen ● 18 days ago

Hi Simon

I finally got it right. I just removed all the parameters that were in the view and then created new ones. My date fields are a timestamp field so i just created a timestamp parameter. Everything works fine now.

Thanks for all your help.

Kind regards,

Dylan

URL

Dylan Cooke-Tonnesen ● 18 days ago

Hi Simon

Just one more question - Is there an easy way to create a copy of my existing SQL views and change it to view where I can create parameters? Or will I have to create a whole new view? I don't want to edit the existing views because I have many clients reports already based on those views.

Regards

Dylan

URL

Simon Karumbi ● 17 days ago

Hi Dylan,

Just like Reports and Dashboards and other assets, you should be able to right click and 'make a copy' of the View.

/ec956ee996ec501dd1b0f81ca9a16237

Let me know if this is what you were after.

Kind regards,

Simon

URL

Simon Karumbi ● 11 days ago

Hi Dylan,

I was just checking in to see if you require any further assistance.

Please don't hesitate to respond here with any queries and I will get back to you shortly.

Otherwise, I will go ahead and mark this ticket as completed.

Kind regards,

Simon

URL

Simon Karumbi ● 30 hours ago

Hi Dylan,

I'm just informing you that I will be closing this ticket due to inactivity.

You are welcome to re-open this ticket by responding here, where I will be able to get back to you shortly.

Kind regards,

Simon

URL