Challenging problem: Is there ways to plot line chart on two X-axis or Dimensions)?

[#1]

QlikView Experts, Please shed some lights on how to approach this problem.

In this case, I want to keep track the weekly cumulative shipment and backlog within one quarter, and the delta at the end of each week (we call it turns and bookings).

Data Structure:

2 dimensions (X-axis) 4 expressions are involved.

Table 1:

Dimension is [Backlog Week], it shows the past weeks within one quarter. 3 Expressions are calculated, the cumulative ship, the weekly delta of total ship+bklg of this quarter (we call it turns), the weekly delta of total ship+bklg of all quarters (we call it bookings). Turns and bookings are calculated using inter chart functions Above() as below:

Expressions 1: 15Q2 Cum Ship

0.000001*Sum( {<[B.Qtr]={'$(vCurrentQtr)'}, [B.Type]={'Ship'},[B.Backlog Week]= >} B.Amount)

Expressions 2: 15Q2 Turns

0.000001*(Sum( {<[B.Qtr]={'$(vCurrentQtr)'}, [B.Backlog Week]= >} B.Amount)-Above(Sum( {<[B.Qtr]={'$(vCurrentQtr)'},[B.Backlog Week]= >} B.Amount)))

Expressions 3: Bookings

0.000001*(Sum({<[B.Backlog Week]= >}B.Amount)-Above(Sum({<[B.Backlog Week]= >}B.Amount)))

Table 2:

Dimension is [Week], it means week 1 to week 14 within one quarter. The relationship between [Backlog Week] and [Week] dimension is that the [Week] dimension contains future week's backlogs. For example, if we are currently at Q2-12 (week 12), then the two dimension means the same till "Q2-12" and "12", and they contains the same cumulative ship amount as of Week 12. But for future week 13 and 14, Table 2 contains week 13 and week 14's bklg amount as of Week 12. For each line of [Backlog Week] in Table 1, there exists a corresponding Table 2, if we are currently at Q2-6 (week 6), then Table 2 has bklgs for week 7 to week 14. The relationship of the two dimension is shown in Table 3.

Expressions 4: 15Q2 Cum Ship+Bklg

0.000001*Rangesum(Above(Total Sum ({<[B.Qtr]={'$(vCurrentQtr)'}>} B.Amount),0,Rowno(Total)))

Table 1 & Table 2:

Table 3:

Problem:

I have been able to plot Chart 1 and Chart 2 based on Table 1 and Table 2, but the requirement is plot all data on one chart based on 1 dimension: Week 1 to Week 14, as shown in Chart 3. (Chart 3 is created by overlaying Chart 1 with transparent background on Chart 2, and force the same scale on 2 charts, and then set x and y-axis color as transparent.)

Besides this workaround, is there ways to plot this 4 expressions on 1 chart?

Any inspirations and solutions is appreciated. Many thanks in advance!

Chart 1:

Chart 2:

Chart 3:

[Gysbert_Wassenaar]

Besides this workaround, is there ways to plot this 4 expressions on 1 chart?

Four expression in one chart isn't the problem. The problem is that you want to use two separate dimensions as one dimension. That is not possible. Perhaps you can change your data model in such a way that you consolidate Backlog Week and Week into one dimension.

[#3]

Thank you for chipping in, Gysbert.

I managed to solve the problem using Valuelist() as a calculated dimension, and merged 4 expressions on this synthetic dimension. I listed examples below. Basic idea is to calculate each row based on the original dimension required.

Besides writing the expressions like below, do you have other possible/better ways to implement this?

Dimension:

=Valuelist('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14')

Example of expresson:

=Pick( RowNo(),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'Q1-14'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-1'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-2'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-3'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-4'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-5'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-6'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-7'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-8'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-9'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-10'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-11'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-12'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-13'}>} [DT_Amount]),

0.000001*Sum ({<$(vSet_Ship_Report_DT)>*<[DT_Qtr]={'$(vCurrentQtr)'}>*<[BacklogQtr-Week]={'$(=Right('$(vCurrentQtr)',2))-14'}>} [DT_Amount])

)