Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
alex-wb
Contributor III
Contributor III

Creating a Target Based of calculations

Hi,

I am new to Qlik sense and need a little bit of help. 

I need to insert a static target based off of the average number of customers  from a financial year over 52 weeks. I believe I need to count the total amount of customers id's first, then sum them, then divide it by 52 weeks to get the average.

So in excel terms it would be IF( Financial_Year ='2020/2021', Sum(count(Customer_ID)/52)," ")

I then would need to create and upper and lower target with a 5% variance each way 

If have the first part created using set analysis: Count({ $<Financial_Year ={'2020/2021'}>}count(Customer_ID)

I have a calendar variable that breaks down our financial weeks that I am using to plot on the graph. 

Open to all suggestions, and really appreciate the help.

Below is a graph of how I envisage it to look.

alex-wb_0-1617985372157.png

I am open to any suggestions and appreciate the help  :).

Thanks,

Alex

1 Solution

Accepted Solutions
alex-wb
Contributor III
Contributor III
Author

Hi Ruben,

I realised that this can be done by using reference lines. I also didn't need to aggregate the count to sum. Thanks for the help though, much appreciated.

View solution in original post

5 Replies
rubenmarin

Hi, you can have a variable that stores the value, like a variable vCustomers2021 with value:

=Count({ $<Financial_Year ={'2020/2021'}>} Customer_ID)

The the upper limit will be: $(vCustomers2021)*1.05 and the lower $(vCustomers2021)*0.95

You can also use TOTAL qualifier to ignore dimensions:

Count(TOTAL { $<Financial_Year ={'2020/2021'}>} Customer_ID)*1.05

alex-wb
Contributor III
Contributor III
Author

Hi Ruben,

Thanks for the reply. Are you saying I should create the basic counts as a variable, so I can then aggregate them in a sum, and then average them out?

Really appreciate the response. 

rubenmarin

Hi Alex, sorry, I forgot to add the "/52" to make the average:

Count([TOTAL] { $<Financial_Year ={'2020/2021'}>} Customer_ID)/52

And you have two ways to avoid dimensions to affect this value: one is using a variable, another is using a TOTAL qualifier

alex-wb
Contributor III
Contributor III
Author

Hi Ruben, thanks for the help, I really appreciate it. I am going to try your suggestions now 🙂

alex-wb
Contributor III
Contributor III
Author

Hi Ruben,

I realised that this can be done by using reference lines. I also didn't need to aggregate the count to sum. Thanks for the help though, much appreciated.

View solution in original post