Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
imsushantjain
Partner - Creator
Partner - Creator

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

Hi,

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

     

Order IDTransactionDatePostDateSalesForecast
101-Aug-1701-Aug-17100110
201-Aug-1702-Aug-17200190
302-Aug-1703-Aug-17300310
403-Aug-1704-Aug-17150160
503-Aug-1705-Aug-17250220
603-Aug-1706-Aug-17350320
704-Aug-1704-Aug-17400370

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

1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

View solution in original post

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
imsushantjain
Partner - Creator
Partner - Creator
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
imsushantjain
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

Your solution worked perfectly!

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Cheers...

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!