TOTAL NOT ACCURATE

[Anonymous]

=sum(UNITS)/count(TOTAL distinct(WEEK))

When I select by one rep it gives me her total weeks, which is 36 with business when actually there were 38 weeks, she had no business in 2 of them.

I am trying to get her average units per week based on all fiscal week available which is 38. Qlik constantly returns her total weeks of 36 and gives me the wrong average.

what am i doing wrong! please help.

I tried All, TOTAL, {1}

please help

FYI Its a KPI Object

[thi_pham]

I assume the dimension which contain 38 weeks is namely BizPeriod, then to count all weeks in BizPeriod which regardless of your rep selected, please try:   count(TOTAL <BizPeriod> distinct WEEK)

[Anonymous]

not sure what you mean by bizperiod

i have no time intelligence in my source file,

[Anonymous]

the dimension is 'week' not bizperiod,

the value is teh week number, the dimension is week

[thi_pham · Accepted solution]

so, it might be

count(TOTAL <rep> distinct WEEK)

The problem is when you select a rep, the count just return number of possible values week (36, instead of 38), then by using total <dimension>, It support to get a better count, please refer: https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/CounterAggregationF...

By using TOTAL [<fld {.fld}>], where the TOTAL qualifier is followed by a list of one or more field names as a subset of the chart dimension variables, you create a subset of the total possible values.

[Anonymous]

=sum(UNITS)/count(TOTAL {<AGENT_LAST_NAME>} DISTINCT WEEK)

THIS DID IT!

Thanks for your help, seems so easy when you see it but when you are new to this it is not.

appreciate the help!

[thi_pham]

you're welcome, I got many difficulty like you when I self-learned qlik sense a year ago, just ask your questions if you have and I will try to support if I can.

[Anonymous]

(sum({1<[FIS YEAR]={'2017'}>} UNITS))-(sum(UNITS))

this should take total units from 2017 ignoring dimension selection, and subtract current 2018 units YTD)

but it does not subtract, it just gives 2017 total units,

what could be wrong?

[Anonymous]

I figured it out but don't know how to fix, it is selecting the whole 2017 fiscal units which i want, but when i filter by repname i want the rep only ttoal units , but not each reps units when I filter by rep name/

[thi_pham]

just remove param 1 in your set analysis: (sum({<[FIS YEAR]={'2017'}>} UNITS))-(sum(UNITS))

Refer how to use 1 and $ here:
https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/SetAnalys...