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

[Anonymous]

this chart says it all

{1) total - gives 52 weels, there is only 33 in fiscal 2018

the KPI object shows 35 av units/week(its dividing by 24, teh actual number of weeks she had businss in)

but the table with the same expression shows the correct amount of an average of 25 units(her name is webbber near the bottom)

I selected her name in the table and this is what happened.

[Anonymous]

[Anonymous]

one more to look at, this time you can see all the formulas, none of it adds up, {1] should give teh range of distinct week which is 33, which teh botttom right corner shows, but with the {1} it delivers 52 which contradicts the bottom right corner , now webber average is correct when I select all reps.....vs filtering by her.

[sunny_talwar]

So, Count(TOTAL DISTINCT Week) is working, right? it is giving us 33?

[Anonymous]

Yes and no 

It is when When I select all sales representatives, but it is not the minute I filter by one sales representative it gives me the wrong number immediately so if for whatever reason is not continuing to count 33 weeks as the total number of weeks, it seems to be counting each individual’s sales representatives number of total weeks as a total weeks which is the very opposite of what that symbol is for

So no ....

You think this is an error in Qlik?

[swuehl]

Guess you need to ignore the sales rep, but consider the possible time range (Year?),  if you select a sales rep, right?

Something like

Sum(UNITS)/ count({1<YearField = p() >} total distinct(WEEK))

You don't Need the total if you use the expression in a KPI without a Dimension.

[Anonymous]

I actually really appreciate the feedback, I am very new to this and still learning and trying to learn of the expression language, I didn’t implement what you suggested and no I did not work he gave me a no answer, or a - 

I can easily build my dashboards, that’s not a problem and I have most of my expressions working well, but this particular one I just discovered is in accurate and it’s most frustrating and I replied ideas from 2/3  different people and none of them work

Theoretically, {1} should provide instructions to bring back the total distinct number of weeks for that particular fiscal year, but it’s bringing out 52 weeks even though 52 weeks has not passed in our fiscal year, we just completed week 33.

Even in the table, with all the other sales representatives listed as the dimension, it actually gives the correct average units per week, But the minute that I apply a filter to choose a single representative, it automatically reverts back to the wrong number

Where the minute that I put into a KPI object and choose a filter of a sales representative immediately it gives the wrong number

I have a dashboard I’m using that when I’m sitting with my sales representatives I can filter by their name and show them all their data, that is where this KPI object is located

But it makes me nervous that some of my calculations might be in accurate, although I think it’s only this one

If you are able to provide further assistance I sure would appreciate it if not I understand

[Anonymous]

Should read “I did apply what you suggest”

[swuehl]

You need to look at your data model and how selections in fields influence the possible set.

So it would really help if you explain your model a bit more detailed.

Seems like you are doing active selections in FIS YEAR and WEEK and that only this combination will lead your expected 33 weeks in fiscal year 2018, is this right? Seems your data shows already 52 weeks in that fiscal year with no selections done.

Try:

=Count({1<[FIS YEAR]= $::WEEK, WEEK = $::WEEK>} DISTINCT WEEK)

Or

=Count({$<AGENT_LAST_NAME = >} DISTINCT WEEK)

Does any of these expressions return the correct number of weeks in a KPI object?