topic Re: Set analysis aggr() expression in QlikView

Set analysis aggr() expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello community,

My dataset looks like:

Customer	RowNumber	BookingDateStart	BookingDateEnd	DaysBetween	Flag	Sales
123	1	5-1-2015	11-1-2015	6	1	100
123	2	1-2-2015	1-3-2015	28	1	150
123	3	10-3-2015	1-4-2015	22	1	100
123	4	1-4-2015	1-10-2015	183	0	100
123	5	9-5-2015	1-10-2015	145	0	150
123	6	1-6-2015	1-8-2015	33	1	100
123	7	24-7-2014	1-6-2015	312	0	100
123	8	1-1-2015	1-9-2015	11	1	150
123	9	1-2-2015	1-11-2015	34	1	100

In the dashboard I want to make an expression looks like below, but it is not working well.

sum( aggr( {< Flag={1}, vMaxDate={'<BookingDateEnd'} >} Sales * (( BookingDateEnd - vMaxDate) / DaysBetween), RowNumber))

I think the part below is not working well. It looks de aggr() goes wrong.

(( BookingDateEnd - vMaxDate) / DaysBetween)

When vMaxDate = 1-6-2015

The Result I want is:

Customer	RowNumber	BookingDateStart	BookingDateEnd	DaysBetween	Flag	Sales	Result €
123	1	5-1-2015	1-11-2015	300	1	100	51
123	2	1-2-2015	1-3-2015	28	1	150	0
123	3	10-3-2015	1-4-2015	22	1	100	0
123	4	1-4-2015	1-10-2015	183	0	100	0
123	5	9-5-2015	1-10-2015	145	0	150	0
123	6	1-6-2015	1-8-2015	61	1	100	100
123	7	24-7-2014	1-6-2015	312	0	100	0
123	8	1-1-2015	1-9-2015	243	1	150	56,79
123	9	1-2-2015	1-11-2015	273	1	100	56,04
263,83

Customer	Result €
123	263,83

Can you help?

Regards,

Arjan

Re: Set analysis aggr() expression

sunny_talwar — Mon, 21 Mar 2016 18:48:09 GMT

May be this:

=Sum({<Flag = {1}, BookingDateEnd = {'>1-6-2015'}>}Sales) * ((BookingDateEnd - '1-6-2015')/(BookingDateEnd - BookingDateStart))

Re: Set analysis aggr() expression

sunny_talwar — Mon, 21 Mar 2016 18:50:48 GMT

Or this with the variable:

=Sum({<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}Sales) * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart))

Re: Set analysis aggr() expression

Anonymous — Mon, 21 Mar 2016 19:06:51 GMT

Thanks for your answer Sunny T, but I'm missing the aggr() in the expression. When I delete the column RowNumber the expression is not working. First the expression must calculate per rownumber the result and then do a Sum()

Re: Set analysis aggr() expression

sunny_talwar — Mon, 21 Mar 2016 19:08:52 GMT

So you only want to see the result as Customer = 123 and sales = 263.83? No break down is needed?

Re: Set analysis aggr() expression

sunny_talwar — Mon, 21 Mar 2016 19:10:42 GMT

This?

Expression:

=Sum(Aggr(Sum({<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}Sales) * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart)), Customer, RowNumber))

Re: Set analysis aggr() expression

Anonymous — Mon, 21 Mar 2016 19:12:18 GMT

For this example I only want to see the results: Customer and Sales.

Re: Set analysis aggr() expression

swuehl — Mon, 21 Mar 2016 19:14:23 GMT

Are you sure you need the advanced aggregation? It should work if you include the days calculation into the sum:

=Sum(

{<Flag = {1}, BookingDateEnd = {'>$(vMaxDate)'}>}

Sales * ((BookingDateEnd - vMaxDate)/(BookingDateEnd - BookingDateStart))

)

Re: Set analysis aggr() expression

sunny_talwar — Mon, 21 Mar 2016 19:18:09 GMT

You are right, I usually have difficult time determining when everything within the Sum would work and when would it not work. I guess its related to aggregation within aggregation kind of concept, right?