topic Re: Straight/Pivot Table Calculation Help in QlikView

Straight/Pivot Table Calculation Help

Tue, 17 May 2016 03:26:50 GMT

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.

Re: Straight/Pivot Table Calculation Help

Gysbert_Wassenaar — Tue, 17 May 2016 15:38:04 GMT

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;

Re: Straight/Pivot Table Calculation Help

Wed, 18 May 2016 11:06:44 GMT

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 🙂