6 Replies Latest reply: Jun 1, 2017 1:21 PM by Mart�n Caruso

# Calculate previous quarter

Good morning!

I have the following data source.

 DIM_AGRUP DIM_YEAR DIM_QUARTER DIM_IMPORT Sales 2016 01 7.060.071 Sales 2016 01 6.734.198 Sales 2016 01 5.278.310 Sales 2015 01 4.382.374 Sales 2015 02 756.017 Sales 2016 02 85.910 Sales 2016 02 560.528 Expenses 2016 01 59.547.876 Expenses 2015 01 15.173.046 Expenses 2016 01 8.990.560 Expenses 2016 02 7.257.139 Expenses 2016 02 6.646.764 Expenses 2016 02 3.857.313 Expenses 2015 02 1.850.429 Expenses 2016 02 775.049

The idea of the board is to be able to select year and quarter in order to obtain as final result the amounts for the selected year, the year prior to the selected year, the selected quarter and the quarter prior to the selected year.

 YEAR_ACT (2016) YEAR_ANT (2015) QUARTER_ACT (2016 - 02) QUARTER_ANT (2016 - 01) Sales 19.719.017 5.138.391 646.438 19.072.579 Expenses 87.074.701 17.023.475 18.536.265 68.538.436

I need to generate the following table in Qlik Sense. So far I have been able to replicate the first three columns. I am not able to obtain the result of the column "QUARTER_ANT".

 QUARTER QUARTER_ANT 201501 201404 201502 201501 201503 201502 201504 201503 201601 201504 201602 201601 201603 201602

It would be very useful the help that I can provide in this regard.

Regards!

• ###### Re: Calculate previous quarter

I think you need to use The As-Of Table here

• ###### Re: Calculate previous quarter

Hello Sunny!

I still can not solve the problem.

Create a variable to which you assign the following value: QuarterName(DIM_QUARTER_NAME-1) [For example: abr-jun 2016]

In the expression of the table, it is the following: sum({ \$<DIM_QUARTER_NAME={\$(=(vQuarterAnt))}> } DIM_IMPORT).

I attach the screenshot with the selectors and with two tables: the "REAL" table is the result that returns the expression I mentioned above. The table "OK" is the result that should give me.

I look forward to your help and appreciate the time you take in it.

Regards!

• ###### Re: Calculate previous quarter

Would you be able to share a sample qvw file you are working with?

• ###### Re: Calculate previous quarter

Sunny, I attached the .qvf file

Thank you!

• ###### Re: Calculate previous quarter

Is this what you want?

Try this if it looks right

Sum({\$<DIM_QUARTER_DATE_START={"\$(=Date(QuarterName(only(DIM_QUARTER_DATE_START)-1)))"}, DIM_YEAR, DIM_QUARTER> } DIM_IMPORT)

• ###### Re: Calculate previous quarter

Excellent Sunny!

Thanks very much!