0 Replies Latest reply: Nov 16, 2016 9:00 AM by m ellis RSS

    RE: Steve Dark - variance to target complexed case

    m ellis

      Hi  Fellow Qlikers,

      I’m looking to use the excellent variance to target  bar chart by Steve.  However my data isn't quite as simple as I do not have one  table with BOTH the target and actuals  in the same table.  What I have is a data warehouse with the actuals based on various set sums using set analysis expressions based upon various field targets within the table. The target is a monthly target. To make matters worse the actual target is an accumulated for  number of dealer that make up the group Dealer, In-house, Overseas or national etc.

       

      Now the data warehouse  has the target sales expressed as the SellerID  next to the sales record. Therefore I sum up all sales for each seller ID to get the actuals then need to link back to the target based on the start of the month and the sellerID.  This is working fine for KPI's except  I cannot produce one graph that has all the targets only one for each target group based on the specific set analysis code for each group.

       

      1. i.e The dealer target would be:

       

      if(ValueList('Under Target','Target','Over Target') = 'Under Target',
      if($(eDealerSales_TM) >= $(eDealerTarget_TM), 0, $(eDealerSales_TM)),
      if(ValueList('Under Target','Target','Over Target') = 'Target',
      if($(eDealerSales_TM) > $(eDealerTarget_TM), $(eDealerTarget_TM),
      if($(eDealerSales_TM) < $(eDealerTarget_TM), $(eDealerTarget_TM) - $(eDealerSales_TM), 0)),
      if(ValueList('Under Target','Target','Over Target') = 'Over Target',
      if($(eDealerSales_TM) > $(eDealerTarget_TM), $(eDealerSales_TM) - $(eDealerTarget_TM), 0))))

       

      and for national

       

      if(ValueList('Under Target','Target','Over Target') = 'Under Target',
      if($(eNationalSales_TM) >= $(eNationalTarget_TM), 0, $(eNationalSales_TM)),
      if(ValueList('Under Target','Target','Over Target') = 'Target',
      if($(eNationalSales_TM) > $(eNationalTarget_TM), $(eNationalTarget_TM),
      if($(eNationalSales_TM) < $(eNationalTarget_TM), $(eNationalTarget_TM) - $(eNationalSales_TM), 0)),
      if(ValueList('Under Target','Target','Over Target') = 'Over Target',
      if($(eNationalSales_TM) > $(eNationalTarget_TM), $(eNationalSales_TM) - $(eNationalTarget_TM), 0))))

      etc 

      Note the respective targets differ by the name of the field being summed up and group it belongs to i.e.

       

      Sum({<TargetPeriod={"$(=(SalePeriod))"},SaleFiscalYear=, [TargetGroup] =, SaleWeekDay=,SaleQuarter=, SaleMonth= >} NationalTarget)

      or

      Sum({<TargetPeriod={"$(=(SalePeriod))"},SaleFiscalYear=, [TargetGroup] =, SaleWeekDay=,SaleQuarter=, SaleMonth= >} DealerTarget)

       

      This there something I need to do at the script level to simply this and generate the required graph or at least have the data appear in one table with the targets and calculated actuals?

       

      Many Thanks

      Mike