see Data from 2017 in 2018

[markus19-85]

Hi Experts,

I have the following problem.

I have two years 2017 and 2018. In 2017 I would like to see the Data like it is for 2017, but in 2018 I would like to see the data from 2018 plus the outstanding bookings from 2017.

Outstanding bookings = all bookings from 2017 which a greater booking date then today but from 2017 (see screen shot)

2017 is correct but 2018 needs to be added by the following bookings

So that I can see 140 for 2018.

Is that possible?

Thanks and Regards,

Markus

[marcus_sommer]

Maybe with something like this:

pick(match(UnderwritingYear, 2018,2017),

    sum({< UnderwritingYear = {2018}> +

             < UnderwritingYear = {2017},

                Booking_Date = {">=$(=addmonths(today(), -12))"}>} total Amount),

    sum(Amount))

- Marcus

[markus19-85]

Thanks that works for the first step. But in my final application I have a dimension before UnderwiritngYear, for example country. I am getting now for one country the amount of both for 2018.

Is there a way to include the dimension as well?

[marcus_sommer]

I would think it should be also working. Could you provide some screenshots of this?

- Marcus

[markus19-85]

Sure

I would like to see 30 for country B in 2018

and is it also possible to see a total row?

Regards,

Markus

[marcus_sommer]

The total needs to be adjusted:

pick(match(UnderwritingYear, 2018,2017),

    sum({< UnderwritingYear = {2018}> +

             < UnderwritingYear = {2017},

                Booking_Date = {">=$(=addmonths(today(), -12))"}>} total <Country> Amount),

    sum(Amount))

- Marcus

[markus19-85]

thanks that's works. Sorry but what do you mean by adjusted? I have no idea how I can see the totals now

[marcus_sommer · Accepted solution]

I meant the total within the expression "total <Country>" and not the TOTAL of the object-column. If you need this total too you need to wrap the expression within an aggr like:

sum(aggr(TheAboveExpression, Country, UnderwritingYear))

- Marcus

[markus19-85]

Thanks a lot, that works 🙂

Regards,

Markus