Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear All,
I have the following data in excel(attached) and i would like to perform the last column calculation logic into Qlikview.
I have included 3 color combination calculations here. The logic for calculation is,
I want to see the change with respect to 2 dimensions i.e Category and Division,
Example, 11.1149% is derived
((Feb 2016 value of Headcount and Business division - Jan 2016 value of Headcount and Business division) / Jan 2016 value of Headcount and Business division)*100
month 2 value - month 1 value of same category and division / month 1 value.
Is it possible to implement this logic in Qlikview Straight/Pivot Table? Can some one please help me ?
I have attached the sample Excel sheet.
Like this:
Temp:
LOAD
Date#([Fiscal Year], 'MMM''YY') as [Fiscal Year],
Quarter,
Catergory,
Division,
Value,
%Change as %Change_xls
FROM
[sample.xlsx]
(ooxml, embedded labels, table is Sheet1);
Result:
LOAD
*,
If(Catergory&Division=Previous(Catergory&Division),100*(Value/Previous(Value)-1)) as %Change
RESIDENT
Temp
ORDER BY
Catergory, Division, [Fiscal Year];
DROP TABLE Temp;
Like this:
Temp:
LOAD
Date#([Fiscal Year], 'MMM''YY') as [Fiscal Year],
Quarter,
Catergory,
Division,
Value,
%Change as %Change_xls
FROM
[sample.xlsx]
(ooxml, embedded labels, table is Sheet1);
Result:
LOAD
*,
If(Catergory&Division=Previous(Catergory&Division),100*(Value/Previous(Value)-1)) as %Change
RESIDENT
Temp
ORDER BY
Catergory, Division, [Fiscal Year];
DROP TABLE Temp;
Dear gwassenaar,
Your answer is best. So simple yet 100% accurate.
Frankly even after so many years leaning and mastering Qlik was not aware of the Previous function.
Thanks a lot for the solution and the knowledge 🙂