Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need some help finishing a line chart.
I have 2 data loads that effectively contain the same data
Costing Table COSP:
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
Costing Table COSS:
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
Each field contains a value for a period. I need to calculate the difference between the fields and calculate a variance % which will be dsplayed in the line chart, I also need to accumulate the values for each period. At he moment I'm trying to use the cross table function so that I can get the periods along the bottom of the chart and the values on the line.
Example
(P1PCGCV0 - P1GCV1) / P1PCGCV0) = Var %
The add P1 and P2 for the P2 point on the chart
I've attached a sample QVD wit the data in. I'm really struggling to get this done any help would be appreciated
I think you should transform your table with The Crosstable Load
- Marcus
I have used the crosstable load. Its the next bit I'm stuck on
How to create the variance calc from the 2 fields and accumulate the values in the line chart
If you load those tables with an approach like this one: Creation of Line chart unless you used now value1 and value2 instead of value and you could in a following load join both tables and then you havbe both values within one table-row and could calculate with them.
- Marcus
HI,
try once
Create two flags
Load * ,'P1' as Flag FROM GCVO;
concatinate
LOAD *, 'P2' as Flag FROM COSS;
Crate a line chart with exp:
(sum({<Flag={'P1'}>} value)-sum({Flag={'P2'}>}value)) / Sum({<Flag={'P1'}>}value)
for accumulation you can select the same option that is available in Expression tab
or else
You can use RangeSum() function for the same
Any chance you could demo in the script for me
Do you mean the COSP load and COSS load should both use value or do you been they should be value1 and value 2
Try something like this:
t1:
CrossTable(Periode, Value1)
LOAD
'Dummy' as Dummy,
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
FROM [Costing Table COSP];
t2:
CrossTable(Periode, Value2)
LOAD
'Dummy' as Dummy,
P01OpeningPlanCostsGCV0,
P01OpeningPlanCostsGCV1,
P01PCGCV0,
P02PCGCV0,
P03PCGCV0,
P04PCGCV0,
P05PCGCV0,
P06PCGCV0,
P07PCGCV0,
P08PCGCV0,
P09PCGCV0,
P10PCGCV0,
P11PCGCV0,
P12PCGCV0
P01PCGCV1,
P02PCGCV1,
P03PCGCV1,
P04PCGCV1,
P05PCGCV1,
P06PCGCV1,
P07PCGCV1,
P08PCGCV1,
P09PCGCV1,
P10PCGCV1,
P11PCGCV1,
P12PCGCV1
FROM [Costing Table COSS];
t3:
Load Periode, Value1 Resident t1;
join
Load Periode, Value2 Resident t2;
t4:
Load *, Value1 - Value2 as ValueDiff Resident t3;
drop tables t1, t2, t3;
- Marcus
Hi
Almost there...but it doesn't like the Join in T3 ? The load just stops...any other ideas ?
Try:
t3:
Noconcatenate Load Periode, Value1 Resident t1;
join
Load Periode, Value2 Resident t2;
- Marcus
Hi
No it still just hangs and won't load