10 Replies Latest reply: Oct 21, 2017 9:29 AM by Philippe BONNIN RSS

    PARTICULAR SYNTAX SUM TOTAL {1}

    Philippe BONNIN

      Hi

       

      i have a table with sectors (named "secteurs") and sales (named "VMS_CATTC")

       

      In the table below you'll see the total sales 2016 for all sectors

      YEAR_TOTAL.png

      As you see the TOTAL 2016 =35444655 , and i trained with different syntaxes using TOTAL or not ...

       

      Now my issue:

       

      several sectors don't produce sales as administration, so if i select administration obviously i have sales = 0

       

      TOTAL ADM.png

       

      Ok .

       

      for reason of calculation of ratios (in others tables) i need an expression which give the result 35444655 EVEN IF  I SELECT only 1 sector producing or not sales ...


      In other words, an expression which give TOTAL SALES OF 2016 regardless of the selected sector ( but with the selection of the year being operational) ... I undertstood that {1}TOTAL expression is not appropriate because of this last point


      i thought that with my Sum({<Flag = {'CY'}>}TOTAL VMS_CATTC) it would be correct , as you see above, it is not ...


      is there a trick using the syntax ?


      thank's in advance


      Philippe

        • Re: PARTICULAR SYNTAX SUM TOTAL {1}
          Stefan Wühl

          Hard to tell the correct syntax without knowing your data model, but in general, you need to use set analysis to ignore selections or modify field selections and there are two basic approaches for your issue:

           

          1) Use set identifier 1 to ignore all selections and then modifiy the field selections where you need to consider user selections

           

          Sum({1<Flag = {'CY'}, Annees = $::Annees) >}TOTAL VMS_CATTC)

          or

          Sum({1<Flag = {'CY'}, Annees = p() >}TOTAL VMS_CATTC)


          First expression will use selected Annees, second will use possible Annees values.


          2) Use set identifier $ to use the current selection state, but ignore specific field selections:


          Sum({<Flag = {'CY'}, SECTEURS= >} TOTAL VMS_CATTC)

            • Re: PARTICULAR SYNTAX SUM TOTAL {1}
              Philippe BONNIN

              Hi Stefan

               

              great thank's to your answer (like always give reponse to the issue but also let us learn ...)

               

              I think that  these expression is the good one for my issue

               

              Sum({1<Flag = {'CY'}, Annees = $::Annees >}TOTAL VMS_CATTC)


              but it seems that it does not work :-(


              2016_SELECT.png

              anything i made wrong ?

                • Re: PARTICULAR SYNTAX SUM TOTAL {1}
                  Stefan Wühl

                  Is the field called Annees or ANNEES (or even different)? Qlik is case sensitive when using field names.

                    • Re: PARTICULAR SYNTAX SUM TOTAL {1}
                      Philippe BONNIN

                      Ok Stefan , your are right . my field is named ‘Year ‘so

                       

                      Sum({1<Flag = {'CY'}, Year= $::Year>}TOTAL VMS_CATTC) is the right expression

                       

                      Unfortunatly, my data model does’nt allow me to use it :

                      In fact, i created a nomenclature table wich is a short list of the global nomenclature that exists in the datas tables imported from ERP .. my goal is to symplify the vizualations for users… using  the short list

                      So when i use the expression, the total calculated is apply on the global nomenclature of course… gasp !

                       

                      Is there a another possibility based on your second solution but in an opposite way  :

                       

                      Sum({<Flag = {'CY'}, SECTEURS= sector 1,sector2>} TOTAL VMS_CATTC) ?


                      Sectors 1, 2… corresponding to my short list

                       

                      Ps : in these expression i don’t see the $ you talk about in your point 2) ?

                      • Re: PARTICULAR SYNTAX SUM TOTAL {1}
                        Philippe BONNIN

                        Maybe with this kind of expression ?

                         

                        Sum([{SetExpression}] [DISTINCT] [TOTAL [<fld {,fld}>]] expr])

                         

                        but i don't understand where write or not { , [ ... ...

                         

                        regards

                          • Re: PARTICULAR SYNTAX SUM TOTAL {1}
                            Philippe BONNIN

                            I think i found the solution for my particular data model

                             

                            let me suggest !

                             

                            i have in my nomenclature table (short list) a field named   MAG. Hors serv.&Presse wich group all the sectors in that short list (excluding unnecessary sectors in the data TABLE)

                             

                            so i use your first suggestion :


                            Sum({1<Flag = {'CY'}, Year = $::Year,ACTIVITE ={'MAG. Hors serv.&Presse'} >}TOTAL VMS_CATTC)

                            it seems working ..


                            what do you think about ?

                      • Re: PARTICULAR SYNTAX SUM TOTAL {1}
                        becki kain

                        what does the p do in this line:

                         

                        Sum({1<Flag = {'CY'}, Annees = p() >}TOTAL VMS_CATTC)


                        and what does Annees = $::Annees (that syntax mean)?  thanks



                        Sum({1<Flag = {'CY'}, Annees = $::Annees) >}TOTAL VMS_CATTC)