Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
martin_caruso
New Contributor III

Calculate previous quarter

Good morning!

I have the following data source.
   

DIM_AGRUPDIM_YEARDIM_QUARTERDIM_IMPORT
Sales2016017.060.071
Sales2016016.734.198
Sales2016015.278.310
Sales2015014.382.374
Sales201502756.017
Sales20160285.910
Sales201602560.528
Expenses20160159.547.876
Expenses20150115.173.046
Expenses2016018.990.560
Expenses2016027.257.139
Expenses2016026.646.764
Expenses2016023.857.313
Expenses2015021.850.429
Expenses201602775.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)
Sales19.719.0175.138.391646.43819.072.579
Expenses87.074.70117.023.47518.536.26568.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".

  

QUARTERQUARTER_ANT
201501201404
201502201501
201503201502
201504201503
201601201504
201602201601
201603201602


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

Regards!

1 Solution

Accepted Solutions

Re: Calculate previous quarter

Is this what you want?

Capture.PNG

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)

6 Replies

Re: Calculate previous quarter

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

martin_caruso
New Contributor III

Re: Calculate previous quarter

Hello Sunny!

Thanks for your answer.

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).


Untitled.png

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?

martin_caruso
New Contributor III

Re: Calculate previous quarter

Sunny, I attached the .qvf file


Thank you!

Re: Calculate previous quarter

Is this what you want?

Capture.PNG

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)

martin_caruso
New Contributor III

Re: Calculate previous quarter

Excellent Sunny!

Thanks very much!