Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey guys,
Hope you can help me with some problems.
I`m trying to display in the same time all sales of different kpi`s for each department id for current day and previous day. But the problem is that when I add them in the same chart it shows me only department id`s from current day.
The script looks something like this.
First , I create a master tabel in which I have all info`s.
temp1:
LOAD
DEPARTMENT_ID,
sum(Value) as Value01 ,
date(floor( DATE))as DATE,
Name_Kpi,
kpiNo
Resident TEMP_TABLE
Group by
DEPARTMENT_ID,
DATE,
Name_Kpi,
kpiNo
;
After this I`m creating the final table which contains the desired data. First in order , is the info for current day:
NoConcatenate
FINAL_TABLE:
LOAD Value01 as Value,
DATE,
DEPARTMENT_ID,
Name_Kpi,
kpiNo,
'CD' as PointInTimeVersion
Resident temp1;
Then I`m adding to this table info for the previous day :
Concatenate(FINAL_TABLE)
LOAD
if((Name_Kpi = Previous(Name_Kpi)) ,Previous(Value01),0) as Value,
DATE,
DEPARTMENT_ID,
Name_Kpi,
kpiNo,
'PD' as PointInTimeVersion,
Resident temp1
Order by
DEPARTMENT_ID,
kpiNo,
DATE
;
When I`m adding all the info from FINAL_TABLE in a pivot table , all values for current day (POINT IN TIME = 'CD') are correct , but for
previous day (POINT_IN_TIME = 'PD') are not so correct.
What I have noticed is that I have some differences on some kpi`s because for the 'PD' are not brought all DEPARTMENT_ID`s. In the table are kept only the DEPARTMENT_ID for 'CD' .
Can you please help me on what to do in script that I can have all values?
Thank you,
Razvan.
Message Edit:
I`m adding an Excel File and a qvw project in which I kinda show what I want to obtain.
The table with two expression is what I get , and in the second table is what I want to have on the second expression from first table. Hope you can help me.
Message was edited by: Razvan Marian Brais