32 Replies Latest reply: Jun 21, 2018 7:55 AM by Rangam Seshadri

# [1}

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

• ###### Re: [1}

Maybe this:

Sum(UNITS)/count(all distinct WEEK)

• ###### Re: [1}

sorry no, tried all, total, your format above, all wrong numbers as answer which I validate manually to ensure.

• ###### Re: [1}

what is interesting is that my formual works in a table, but not in a KPI onbject. Exact same formula.

• ###### Re: [1}

Would you be able to share a sample or an image to show it working in table, but not in KPI?

• ###### Re: [1}

look at "webber" then look at 2018 av/wk is 25.956

here it is in the KPI object, exact same expression:  sum(units)/count(total distinct(week))

• ###### Re: [1}

here it is 36 becasue it is dividingby her total number of weeks biut not by the actual number of weeks, I thought "total" would do that.

• ###### Re: [1}

look at webber and avunits/wk

• ###### Re: [1}

same exact expression:

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

• ###### Re: [1}

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

• ###### Re: [1}

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

• ###### Re: [1}

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.

• ###### Re: [1}

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.

• ###### Re: [1}

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.

• ###### Re: [1}

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

• ###### Re: [1}

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.

• ###### Re: [1}

no 52 weeks in 2018

• ###### Re: [1}

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

• ###### Re: [1}

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?

• ###### Re: [1}

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.

• ###### Re: [1}

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

• ###### Re: [1}

Should read “I did apply what you suggest”

• ###### Re: [1}

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?

• ###### Re: [1}

This is what worked, I worked on it a lot, sadly used a lot of time for something seemingly simple.

You ahve to forgive me, I am new ot teh BI softtare world, i taugh myself excel, then tableau, then qliksense, then micrsoft power BI, and fell in love with qliksense cloud.............

i have no issue with the UI, the basic set up and creating visualizations, but when it comes to the set analysis, expressions etc.........gosh that is hard to learn, I have watched videos, read articles, and really there is no one plave to go that is designed for complete newbies like me to get understanding of all the syntax in expressions, etc

I wish there was.

• ###### Re: [1}

Good to hear that it worked for you.

Note that this will hard code your week calculation to a certain fiscal year, not sure if this is what you want.

To get started, I would recommend the QlikView for developers book, currently e-book on sale:

QlikView for Developers | Now just \$10

Most of it (Scripting / set Analysis) is also valid for Sense.

Besides this, my first expression above should have been written as

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

• ###### Re: [1}

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.

• ###### Re: [1}

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.

###### you can use his (Stefan Wüh) expression like below so that the calculations will not change even after you select only Webber or all.

sum(UNITS)/Count({<[FIS YEAR]={'2018'},AGENT_LAST_NAME = >} DISTINCT WEEK),

Note: AGENT_LAST_NAME  is the sales rep filed name.