topic Re: Planvalues in a Pivot table in QlikView

Planvalues in a Pivot table

pacoli2013 — Tue, 31 Jul 2018 12:43:19 GMT

Hello,

I have an expression

if (Max(Month) > (StartMonat +2), (Sum([Wert PE Plan]) / (12 - (StartMonat +2))) * (Max(Month) - (StartMonat +2)), 0)

When i combine this expression with customerid it works fine because every customer has it‘s own startmonth and planvalue.

When I combine it with salesdistrictgroup and salesdistrictid it doesn‘t work because a salesdistrictgroup and / or salesdistrictid has more then one startmonth and planvalue

a salesdistrictgroup can have more then one salesdistrictid. And one salesdistrictid can have more then one customerid. Customerid is the lowest level.

The if-statement is needed because planvalues are only activated two months after startmonth. For example: when startmonth is january and max-month is february the planvalue to be calculated is 0, when max-month is april the planvalue which should be taken is the planvalue belonging to April and belonging with that salesdistrictgroup / salesdistrictid / customer.

With mine Expression the values when calculated are not correct (see tablebox)

An other problem can be that startmonth is 0. When startmonth is = 0 then the customes has no priceincreases. otherwise proceincreases starts 2 month after the startmonth

I want to build this expression into a pivottable with dimensions salesdistrictgroup and salesdistrictid

I hope someone can solve the problem (if it can besolved) and can give me the right expression.

For testing I have added a little qvw with a pivot table and a table box

Thands in advance

regards Court

Re: Planvalues in a Pivot table

sunny_talwar — Tue, 31 Jul 2018 12:50:59 GMT

Not entirely sure what you might need... but see if this works

=Sum(Aggr(If(Max(Month) > (StartMonat+2), (Sum(PePlan) / (12 - (StartMonat +2))) * (Max(Month) - (StartMonat +2)), 0), AX_SalesDistrictGroup, AX_SalesDistrictID, AX_CustomerID))

Re: Planvalues in a Pivot table

pacoli2013 — Tue, 31 Jul 2018 13:07:14 GMT

Hello Sunny,

thanks I will check it,

I'll come back with the results

Court

Re: Planvalues in a Pivot table

pacoli2013 — Wed, 01 Aug 2018 07:32:20 GMT

Hello Sunny,

I checked your Aggr-function: it works !!

Thanks

I wished my knowledge about Set Analysis was so good as yours.

Re: Planvalues in a Pivot table

sunny_talwar — Wed, 01 Aug 2018 11:15:42 GMT

What I used is not set analysis, but Aggr() function... read more on this topic here

https://www.analyticsvidhya.com/blog/2014/02/aggr/

Re: Planvalues in a Pivot table

pacoli2013 — Thu, 02 Aug 2018 11:57:57 GMT

Hello Sunny,

I will read the document about Aggr() function.

I thought the Aggr-function was part of Set Analysis.

Re: Planvalues in a Pivot table

sunny_talwar — Thu, 02 Aug 2018 11:58:35 GMT

Nope, they are two very different things

Re: Planvalues in a Pivot table

BalaBhaskar_Qlik — Thu, 02 Aug 2018 14:34:27 GMT

May be you can use like this:

Sum(Aggr(sum({$<Field1={'3'},Field2={'AP'},Date={"Max(Date)"}>}Total), $(vDIM)))