Straight/Pivot Table Calculation Help

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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;

[#3]

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 🙂