topic Re: see Data from 2017 in 2018 in QlikView

see Data from 2017 in 2018

markus19-85 — Tue, 26 Jun 2018 09:19:13 GMT

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

Re: see Data from 2017 in 2018

marcus_sommer — Tue, 26 Jun 2018 10:47:42 GMT

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

Re: see Data from 2017 in 2018

markus19-85 — Tue, 26 Jun 2018 11:45:39 GMT

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?

Re: see Data from 2017 in 2018

marcus_sommer — Tue, 26 Jun 2018 11:53:36 GMT

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

- Marcus

Re: see Data from 2017 in 2018

markus19-85 — Tue, 26 Jun 2018 11:59:44 GMT

Sure

I would like to see 30 for country B in 2018

and is it also possible to see a total row?

Regards,

Markus

Re: see Data from 2017 in 2018

marcus_sommer — Tue, 26 Jun 2018 12:20:09 GMT

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

Re: see Data from 2017 in 2018

markus19-85 — Tue, 26 Jun 2018 12:30:36 GMT

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

Re: see Data from 2017 in 2018

marcus_sommer — Tue, 26 Jun 2018 12:48:05 GMT

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

Re: see Data from 2017 in 2018

markus19-85 — Tue, 26 Jun 2018 13:05:01 GMT

Thanks a lot, that works 🙂

Regards,

Markus