0 Replies Latest reply: Apr 12, 2018 2:55 AM by vikas mahajan RSS

    How to show Grand total line in line chart Qlik Sense

    vikas mahajan

      hi all,

       

      I wanted to show total line in line chart my chart dimension and measures as follows

       

      Thanks

       

      Vikas

       

      dim : =Pick(Match($(vSalesView),1,2,3),MonthName,Quarter, if(FinancialYear >='2012-13',FinancialYear))
                      &      Company

      measure :

      Pick(Match($(vMeasure),5,2,3,4,1,6),
      // Sales Volume
      ( if($(vSalesView)=1  or $(vSalesView)=2,if((
            SUM({$<FinancialYear=,Quarter=,FiscalYear={$(=max(FiscalYear))},
                                                  [Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}Y2Sale)/$(vDivide))>0,
             num(SUM({$<FinancialYear=,Quarter=,FiscalYear={$(=max(FiscalYear))},
          [Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}Y2Sale)/$(vDivide),'##,###.#')
         ,''), num(sum({$<FinancialYear=>} Y2Sale)/$(vDivide),'##,###.#')  ))  ///num(sum(Y2Sale)/$(vDivide),'###.#')


      ,// Mop-UP Sale

      if($(vSalesView)=1  or $(vSalesView)=2,
      num(
      (
      Sum({<FiscalYear={$(=max(FiscalYear))} >}SALES_IN_KL_CURRENT)
      -
      SUM({$<FinancialYear=,Quarter=,FiscalYear={$(=max(FiscalYear))},
      [Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}
           SALES_IN_KL_LAST_YEAR)
      //sum({<FiscalYear={$(=max(FiscalYear)-1)},YEAR_MONTH=p(YEAR_MONTH_PREVIOUS)>}SALES_IN_KL_LAST_YEAR)
      )/$(vDivide)
      ,'##,###.#')

      ,
          if(GetSelectedCount(FinancialYear)=0,
           num((Sum(SALES_IN_KL_CURRENT)-sum(SALES_IN_KL_LAST_YEAR))/$(vDivide),'#,###.#'),
           num(
           (sum({$ <FinancialYear=>} SALES_IN_KL_CURRENT-SALES_IN_KL_LAST_YEAR)/$(vDivide)
               )
              
               ,'##,###.#'))
         
         
          )

       


      , //Market Share
           //Option 3 Market Share
      if($(vSalesView)=1,    
         num(((Sum({<FiscalYear={$(=max(FiscalYear))}>}Y2Sale)/aggr(nodistinct Sum ({<FiscalYear={$(=max(FiscalYear))},COMP=>}Y2Sale),MonthName)))*100,'##.##'),
         if($(vSalesView)=2,
         //num(((Sum(Y2Sale)/aggr(nodistinct Sum ({<COMP=>}Y2Sale),Quarter))),'#,##0.0%'),
         num(((Sum({<FiscalYear={$(=max(FiscalYear))}>}Y2Sale)/aggr(nodistinct Sum ({<FiscalYear={$(=max(FiscalYear))},COMP=>}Y2Sale),Quarter)))*100,'##.##'),

      if($(vSalesView)=3,

           num(((Sum(Y2Sale)/aggr(nodistinct Sum ({<COMP=>}Y2Sale),FinancialYear) ))*100 ,'##.##')
       

      )))
      // num(Sum({<FinancialYear=,Quarter=,MonthName=,Year={">=$(=Max(Year)-6)<=$(=Max(Year))"}>} Y2Sale )/$(vTotY2Sale),'#,##0.0%')  )))


      ,
      // Market Share (+/-)

      if($(vSalesView)=1,    
          // Monthly

      num(((Sum({<FiscalYear={$(=max(FiscalYear))}>}Y2Sale)
      /aggr(nodistinct Sum ({<FiscalYear={$(=max(FiscalYear))},COMP=>}Y2Sale),MonthName)))*100
      -
      ((SUM({$< FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=
                  ,MonthName=>}Y2Sale)        

      /aggr(nodistinct
      SUM({$< FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=
                  ,MonthName=,COMP=>}Y2Sale)
                 
                  ,MonthName)))*100,'##.##')
                 
          ,
         if($(vSalesView)=2,
         // Quarterly
             num(((Sum({<FiscalYear={$(=max(FiscalYear))}>}Y2Sale)
      /aggr(nodistinct Sum ({<FiscalYear={$(=max(FiscalYear))},COMP=>}Y2Sale),Quarter)))*100
      -
      ((
      SUM({$< FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=
                  ,Quarter=>}Y2Sale)        

      /aggr(nodistinct
      SUM({$< FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={'<=$(vLMaxDate)'},FinancialYear=
                  ,Quarter=,COMP=>}Y2Sale),Quarter)))*100,'##.##')
      ,
         //Yearly
          if($(vSalesView)=3   ,  num(
                                         ((Sum(SALES_IN_KL_CURRENT)/aggr(nodistinct Sum( {<COMP=>} SALES_IN_KL_CURRENT),FiscalYear))
                                          -  (Sum(SALES_IN_KL_LAST_YEAR)/aggr(nodistinct Sum( {<COMP=>} SALES_IN_KL_LAST_YEAR),FiscalYear)))*100
                                             ,'##.##')


      )))

      ,

      // Growth %
      if($(vSalesView)=1  or $(vSalesView)=2,
      num((((
      SUM({$<FinancialYear=,Quarter=,FiscalYear={$(=max(FiscalYear))},
      [Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),0,4))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}>}
           Y2Sale)
      )
      -
      Sum({$<FinancialYear=,FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>}
            Y2Sale)
           
            )

      /Sum({$<FinancialYear=,FiscalYear={$(=max(FiscalYear)-1)},[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-1,4))<=$(=addmonths(max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]),-12))"}>} Y2Sale))*100,
      '##.##')
      ,
      num(
      ((sum({<FinancialYear=>} SALES_IN_KL_CURRENT)-sum({<FinancialYear=>} SALES_IN_KL_LAST_YEAR))/sum({<FinancialYear=>}SALES_IN_KL_LAST_YEAR))*100
      ,'##.##'))
      ,
      )