Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
martin_caruso
Contributor III
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
sunny_talwar

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)

View solution in original post

6 Replies
sunny_talwar

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

martin_caruso
Contributor III
Contributor III
Author

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!

sunny_talwar

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

martin_caruso
Contributor III
Contributor III
Author

Sunny, I attached the .qvf file


Thank you!

sunny_talwar

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
Contributor III
Contributor III
Author

Excellent Sunny!

Thanks very much!