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

    [1}

    Kenneth Holden

      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.

       

        • Re: [1}
          Thiago Justen Teixeira

          Maybe this:

           

          Sum(UNITS)/count(all distinct WEEK)

          • Re: [1}
            Sunny Talwar

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

              • Re: [1}
                Kenneth Holden

                Screenshot of Google Chrome (2018-06-18, 7-18-48 PM).png

                • Re: [1}
                  Kenneth Holden

                  look at webber and avunits/wk

                  • Re: [1}
                    Kenneth Holden

                    same exact expression:

                     

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

                      • Re: [1}
                        Sunny Talwar

                        How about this in KPI object

                         

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

                          • Re: [1}
                            Kenneth Holden

                            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}
                                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.

                                  • Re: [1}
                                    Kenneth Holden

                                    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}
                                        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.

                                          • Re: [1}
                                            Kenneth Holden

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

                                            • Re: [1}
                                              Kenneth Holden

                                              Screenshot of Google Chrome (2018-06-18, 8-12-50 PM).png

                                              • Re: [1}
                                                Kenneth Holden

                                                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}
                                                  Kenneth Holden

                                                  no 52 weeks in 2018

                                                  • Re: [1}
                                                    Kenneth Holden

                                                    Screenshot of Google Chrome (2018-06-18, 8-44-16 PM).png

                                                    • Re: [1}
                                                      Kenneth Holden

                                                      Screenshot of Google Chrome (2018-06-18, 8-54-11 PM).png

                                                        • Re: [1}
                                                          Sunny Talwar

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

                                                            • Re: [1}
                                                              Kenneth Holden

                                                              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}
                                                                  Stefan Wühl

                                                                  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}
                                                                      Kenneth Holden

                                                                      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}
                                                                        Kenneth Holden

                                                                        Should read “I did apply what you suggest”

                                                                          • Re: [1}
                                                                            Stefan Wühl

                                                                            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}
                                                                                Kenneth Holden

                                                                                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.

                                                                                  • Re: [1}
                                                                                    Stefan Wühl

                                                                                    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}
                                                                    Kenneth Holden

                                                                    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}
                                                                      Kenneth Holden

                                                                      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.

                                                                        • Re: [1}
                                                                          Rangam Seshadri
                                                                          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.