How to show line column combination based on two fields based on two different types of dates in a single chart

[imsushantjain]

Hi,

I have my fact data in this format and i can't bifurcate it into two tables:

Order ID	TransactionDate	PostDate	Sales	Forecast
1	01-Aug-17	01-Aug-17	100	110
2	01-Aug-17	02-Aug-17	200	190
3	02-Aug-17	03-Aug-17	300	310
4	03-Aug-17	04-Aug-17	150	160
5	03-Aug-17	05-Aug-17	250	220
6	03-Aug-17	06-Aug-17	350	320
7	04-Aug-17	04-Aug-17	400	370

Can I show Sum(Sales) For TransacationDate as Column and Sum(Forecast) for PostDate as line on a single chart with common date dimension.

i.e for 01-Aug-17, Sum(Sales) on TransactionDate will be 300 but Sum(Forecast) will be 110 and will be displayed on a common date dimension on x -axis.

There is a similar post on this this, but data in that case is in two different tables : Re: How to show two lines based on two fields based on two different types of dates

Thanks

Sushant

[kaushiknsolanki]

You can change your script a little bit and then you can achieve this.

Try following.

Load Order_ID, Transaction_Date as Date, Sales as Value, 'Sales' as Source

From xyz;

Concatenate

Load Order_ID, Post_Date as Date, Forecast as Value, 'Forecast' as Source

From xyz;

Then you can create a chart with Date as dimension and following 2 expression.

1. Sales

Sum({<Source = {"Sales"}>}Value)

2. Forecast

Sum({<Source = {"Forecast"}>}Value)

Regards,

Kaushik Solanki

[imsushantjain]

Hey Kaushik,

Actually your solution is good but we can't implement it. We have a lot of logic and calculation for KPI's based on both the dates in the load scripting, so that will get heavily impacted and complicated.

Thanks

Sushant

[kaushiknsolanki · Accepted solution]

Try this.

I am not changing the base table but introducing a new table to get the dates.

Data:

LOad *,Floor(TransactionDate)&'-'&Floor(PostDate) as LinkKey inline [

Order ID, TransactionDate, PostDate, Sales, Forecast

1, 01-Aug-17, 01-Aug-17, 100, 110

2, 01-Aug-17, 02-Aug-17, 200, 190

3, 02-Aug-17, 03-Aug-17, 300, 310

4, 03-Aug-17, 04-Aug-17, 150, 160

5, 03-Aug-17, 05-Aug-17, 250, 220

6, 03-Aug-17, 06-Aug-17, 350, 320

7, 04-Aug-17, 04-Aug-17, 400, 370

];

Link:

Load Distinct Floor(TransactionDate)&'-'&Floor(PostDate) as LinkKey,

TransactionDate as Date,

'Sales' as Source

Resident Data;

Load Distinct Floor(TransactionDate)&'-'&Floor(PostDate) as LinkKey,

PostDate as Date,

'Forecast' as Source

Resident Data;

In chart use Date as dimension and expression as below

1. Sales

Sum({<Source = {"Sales"}>}Sales)

2. Forecast

Sum({<Source = {"Forecast"}>}Forecast)

Regards,

Kaushik Solanki

[imsushantjain]

This is indeed a brilliant solution, thanks a lot Kaushik!. I am yet to implement it, will let you know if i face any particular issue.

[imsushantjain]

Your solution worked perfectly!

[kaushiknsolanki]

Cheers...

Regards,

Kaushik Solanki