Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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)
I think you need to use The As-Of Table here
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).
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!
Would you be able to share a sample qvw file you are working with?
Sunny, I attached the .qvf file
Thank you!
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)
Excellent Sunny!
Thanks very much!