topic Re: Creating a Target Based of calculations in App Development

Creating a Target Based of calculations

alex-wb — Fri, 09 Apr 2021 16:26:28 GMT

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

Re: Creating a Target Based of calculations

rubenmarin — Sat, 10 Apr 2021 08:41:36 GMT

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

Re: Creating a Target Based of calculations

alex-wb — Sat, 10 Apr 2021 08:47:18 GMT

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.

Re: Creating a Target Based of calculations

rubenmarin — Sat, 10 Apr 2021 18:14:35 GMT

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

Re: Creating a Target Based of calculations

alex-wb — Mon, 12 Apr 2021 07:57:19 GMT

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

Re: Creating a Target Based of calculations

alex-wb — Fri, 16 Apr 2021 09:45:06 GMT

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.