Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kennethholden
Contributor

Simple but Perplexing

((sum({1<[FIS YEAR]={'2017'}>} UNITS))-(sum(UNITS)))/(52-(count(distinct(WEEK))))

The above expression is designed to give me how many "units per week" a sales rep has to write in order to be on par with all of last years total units.

it does give me an average but its off by about 5%, when I do do the math manulaly this expresion works perfectly.

I independantly tested each side of the equation in a KPI object and  when done indepednantly they both give teh exact correct ansewer. Howver, when i put them into teh expression above togetehr the average is slightly off.

This is perplexing.

can anyone assist?

52- count(distinct(week-) gives me the number of weeks thatremain, and the first part of the equation gives  the  year to date unit production from the last years total number.

Thank you.

4 Replies
MVP
MVP

Re: Simple but Perplexing

If it is used to give an indication of how much needs to be sold in the beginning of a week shouldn't really the calculation be:

( Sum({1<[FIS YEAR]={'2017'}>} UNITS) - Sum(UNITS) ) / ( 53 - Count( DISTINCT WEEK) )


You would even get a division by zero at the beginning of week 52 if you stick to your original calculation... But you might even get that with 53 because some years have week 53 also.


I am assuming that your are referring to calendar weeks....

kennethholden
Contributor

Re: Simple but Perplexing

I will try this.

Not calendar year, fiscal year, and yes some have 53 even in fiscal years.

kennethholden
Contributor

Re: Simple but Perplexing

No, this actually made the gap slightly larger, foing 51 actually gave the right number but only in some cases.

This is why this is perplexing, as each side of teh equation gave teh right resulgt in a KPI object, so logically you would think dividing one by teh other would tiled the exact result.

Doing it on a calutator gives the right outcome.

sasiparupudi1
Honored Contributor III

Re: Simple but Perplexing

please provide some example numbers showing the difference seen between qlik and the calculator..

may be this is not a rounding issue?

Community Browser