Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mrogers1
New Contributor

Subtracting Fiscal Periods

Trying to find a way in the pivot table expression to subtract a month from the previous month before it for all fiscal periods. We are currently pulling in each month as a YTD number so subtracting the previous months YTD # gives us the true monthly number. For instance- Period 4 is the sum of period 1-4 minus the sum of period 1-3. I feel like there should be a simple way to do this within the expression but I cannot figure it out for every single month.

I am still very new to this software so all help is appreciated.

4 Replies
Digvijay_Singh
Honored Contributor III

Re: Subtracting Fiscal Periods

If possible please share dimensions, measures, sample app, data or expected output.

mrogers1
New Contributor

Re: Subtracting Fiscal Periods

Hi Digvijay, thanks for the response-

The Pivot table is set up with the following:

The Rows are: Region, Entity Name, and Plant

The Column is: Fiscal_Period

and the Measures are: Actuals, Budget, Var to Budget, and % Var to Budget

How the Data Comes into Qlik

Fiscal_Period 1 = 100 (The first Period will always pull correct)

Fiscal_Period 2 = 200

Fiscal_Period 3 = 300

Desired Output to calculate within Qlik Expression:

Fiscal_Period 1 = 100

Fiscal_Period 2= Fiscal_Period 2 - Fiscal_Period 1 = 200 - 100 = 100

Fiscal_Period 3= Fiscal_Period 3 - Fiscal_Period 2 = 300 - 200 = 100


Not sure if this is something I need to be doing in the script or if it is possible to do within the expression in the actual visualizations?

Thanks in advance,

Mike

mrogers1
New Contributor

Re: Subtracting Fiscal Periods

Anyone have any thoughts on how to accomplish this?

Thanks,

Mike

sasiparupudi1
Honored Contributor III

Re: Subtracting Fiscal Periods

may be use the above function

Sum(Amount)-Above(Sum(Amount))

Community Browser