Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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

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

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

6 Replies

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

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

Partner
Partner

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

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

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

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

Partner
Partner

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

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.

Partner
Partner

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

Your solution worked perfectly!

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

Cheers...

Regards,

Kaushik Solanki