3 Replies Latest reply: May 31, 2017 10:34 PM by Weilin Tan RSS

    Combine YTD Actual and YTD Target sales in line chart

    Weilin Tan

      Hi All,

       

      I am having problems combining 2 expressions from 2 tables together in a single line chart to show monthly YTD. My sales information are all monthly.

       

      Dimension used: Month (from 2017 Actual sales table. Jan, Feb, Mar, ..., Dec and YTD)

      First expression= 2017 YTD Actual sales

      Second expression= 2017 YTD Target sales (Target sales table has "month" column renamed as "Month_Target" to avoid synthetic keys, but months available are the same, with exception of YTD)

       

      Each expression works fine on their own with their respective "Month" or"Month_Target" as dimension but when combined by using  "Month" from Actual Sales table as the only dimension, the "2017 YTD Target Sales" expression fail to show... Plase advice. Thank you!

        • Re: Combine YTD Actual and YTD Target sales in line chart
          Michele De Nardi

          Can you upload some example data ?

          • Re: Combine YTD Actual and YTD Target sales in line chart
            Jonathan Dienst

            This is why it is better to load both the targets and actuals into the same table using the same date field. In your case, the dates are disconnected, and the target and actual values are not associated correctly. While it may be possible to get this working without fixing the data structure, it will always be slow (which may not be a problem if your data model is small) and complicated.

             

            Rather fix the data structure and then this becomes simple and efficient. Here is some psuedo-code to help

             

            //Load actuals

            Fact:

            LOAD TransID,

                 Amount,

                 TranDate,

                 ...

                 'Actual' as TranType

            FROM <source for Actuals>

             

            //Load targets

            Concatenate(Fact)

            LOAD Target,

                 TargetDate as TranDate,

                 ...

                 'Target' as TranType

            FROM <source for Targets>

             

            Now expression for actuals includes set expresssion clause: TranType = {Actual} and targets TranType = {Target}.

            If the actuals are daily and targets are monthly, align the date granularity by using something like:

             

                 Date(MonthStart(TargetDate), 'MMM yyyy') as TranDate,