Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Still getting used to Qlik so please forgive me if this is an easy question.
I'm trying to calculate a value based on two values loaded into a straight column
month_end_date | ftp_rpt_level_4 | NII - Mortgage Fixed | day(month_end_date) |
2020-07-31 | Mortgage - Fixed | $2,290,872 | 31 |
2020-08-31 | Mortgage - Fixed | $2,421,531 | 31 |
2020-09-30 | Mortgage - Fixed | $2,422,785 | 30 |
2020-11-30 | Mortgage - Fixed | $2,586,063 | 30 |
2020-10-31 | Mortgage - Fixed | $2,610,914 | 31 |
2020-12-31 | Mortgage - Fixed | $2,919,318 | 31 |
2021-02-28 | Mortgage - Fixed | $3,040,461 | 28 |
2021-06-30 | Mortgage - Fixed | $3,225,080 | 30 |
2021-01-31 | Mortgage - Fixed | $3,304,495 | 31 |
2021-07-31 | Mortgage - Fixed | $3,380,276 | 31 |
2021-04-30 | Mortgage - Fixed | $3,388,306 | 30 |
2021-03-31 | Mortgage - Fixed | $3,547,119 | 31 |
2021-05-31 | Mortgage - Fixed | $3,550,501 | 31 |
I would like to be able to calculate NII - Mortgage Fixed divided by day(month_end_date) for each of the months listed and I'm stuck about the next steps. I'd prefer if the calculation can be done as an expression rather than ammending load data.
@GrantBooth try below
Sum({<ftp_rpt_level_4 = {"Mortgage - Fixed"}>} nii_00_gross_interest)/
day(aggr(only({<ftp_rpt_level_4 = {"Mortgage - Fixed"}>} month_end_date),month_end_date,ftp_rpt_level_4))
Got to Add-on in table properties and uncheck "Include zero values" option
you can do it by this expression :
column(3)/ column(4)
or you can use the fields titles
[NII - Mortgage Fixed]/[day(month_end_date)]
Thanks for the response, your answer makes it seem as basic as I would have expected however I simply can't make it work.
To give further context, the NII column is the result of a filtered sum
Sum({<ftp_rpt_level_4 = {"Mortgage - Fixed"}>} nii_00_gross_interest)
And the Days field is just the day funtion
=day(month_end_date)
I have tried adding a new column as both a measure and a dimension with no results
I've tried just a basic operator to column three, Column(3) + 1, and I still get no information. I feel like I'm missing something but I can't for the life of me work out what
@GrantBooth what do you get if you use below as measure?
Sum({<ftp_rpt_level_4 = {"Mortgage - Fixed"}>} nii_00_gross_interest)/day(month_end_date)
So it turns out that the results were there, I was just not scrolling in the table. It's working now, however now I have a new problem in that all of the values of column 2 are being returned, not just the ones I wanted to filter on
So now I've got the new problem of removing the unwanted rows
@GrantBooth try below
Sum({<ftp_rpt_level_4 = {"Mortgage - Fixed"}>} nii_00_gross_interest)/
day(aggr(only({<ftp_rpt_level_4 = {"Mortgage - Fixed"}>} month_end_date),month_end_date,ftp_rpt_level_4))
Got to Add-on in table properties and uncheck "Include zero values" option