Combine YTD Actual and YTD Target sales in line chart

[#1]

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!

[micheledenardi]

Can you upload some example data ?

[jonathandienst]

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,

[#4]

Thank you! I actually concatenated the target to the fact table and it actually worked perfectly. Knew that there got to be a simple solution around this!