Creating a Target Based of calculations

[alex-wb]

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.

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

Thanks,

Alex

[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]

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]

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

[alex-wb · Accepted solution]

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.