Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

[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

Your help apprecaited.

32 Replies
Anonymous
Not applicable
Author

Screenshot of Google Chrome (2018-06-20, 2-09-13 PM).png

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.

thanks for your help.

swuehl
MVP
MVP

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)

rangam_s
Creator II
Creator II

you can use his (swuehl) 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.