[1}

[Anonymous]

Sum(UNITS)/count({1}total distinct(WEEK))

This does not return the correct answer. Essentually I have 33 weeks of the fiscal year so far and for this sales representative she has only written busines in abot 20, so qkiksense is ging me her average per week based only on the weeks she rote business, infalting her true average per week which is divided by all weeks.

I attemped to to put the {1) in here to tell qlik to include all totoal distinct weeks YTD and it gives a me weird and wrong number

Your help apprecaited.

[Anonymous]

same exact expression:

sum(units)/count(total distinct(week))

[sunny_talwar]

How about this in KPI object

Sum(units)/Count({1} TOTAL DISTINCT week)

[Anonymous]

I did that myself also, and same result, it counts by the weeks she actually wrote business(24) vs actual fiscal weeks of 33).

so weird

[sunny_talwar]

‌I think you need to break down your expression to see what your denominator is doing in the chart and the KPI... use this in both the locations Count({1} TOTAL DISTINCT week) and check its value. See what the difference is.

[Anonymous]

in my source file(excel), I have just named the column week, and it has just numbers 1,2,3,4,5,6,7,8, etc,

could this be the problem where it is not acvtually classified as a week?

if this is the issue, how do i tell excel to recognize the week as a week, I have no experience or knowledge with calendar related concepts.

I did both stand alone and both yielded the number divided by 24 weeks, vs the actual number of weeks is 33.

[sunny_talwar]

‌So both the chart and KPI shows 24? Then how come the chart shows the right number, but KPI doesn’t? Something to do with numerator?

i don‘t think reading them as number should be anu issue.

[Anonymous]

sorry , my mistake, both read as 35, the incorrect number, in both case

[Anonymous]

[Anonymous]

I tested something else out, even though I am filtering by 2018 fiscal year(at week 33), when i type in (all distinct(week) it dvides by 52, but we have not had 52 distinct in fiscal 2018

same when I use {1} it devides by 52 but no 5 weeks in 2018.

[Anonymous]

no 52 weeks in 2018