Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
imsushantjain
Partner
Partner

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

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

6 Replies
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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
imsushantjain
Partner
Partner
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

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
imsushantjain
Partner
Partner
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
Partner
Author

Your solution worked perfectly!

kaushiknsolanki

Cheers...

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".