Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
stanlyrj
New Contributor III

P&L Reporting with Simulation using variable

Hi Community,

Here I'm trying to create P&L report including some variables to simulate different scenarios.

Here I have the P&L structure configured as shown in the below image.

P&L Structure.PNG

What I'm trying to create a table as shown as in the below image.

Desiered Table

Simulation Expected Output.PNG

The expression I have used for calculating [Amount*Variable] =

aggr(sum(amount),[P&L Layout],[Row No])*sum(pick(match(P&L Lines,Variable),Varaible))

The resulting table is as shown in the below image.

Current Table

Simulation output.PNG

Can anyone suggest any possible solution to solve this problem?

Any help is greatly appreciated.

Thanks in advance.

Stanly

1 Solution

Accepted Solutions
stanlyrj
New Contributor III

Re: P&L Reporting with Simulation using variable

Solved!!

Used an expression like this

sum(

     aggr(sales*variable,[P&L Lines],[P & L Layout], [Row No]) -

     aggr(costs*variable,[P&L Lines],[P & L Layout], [Row No]) -

     aggr(personal costs*variable,[P&L Lines],[P & L Layout], [Row No])

)

Simulation now works perfectly.......................

1 Reply
stanlyrj
New Contributor III

Re: P&L Reporting with Simulation using variable

Solved!!

Used an expression like this

sum(

     aggr(sales*variable,[P&L Lines],[P & L Layout], [Row No]) -

     aggr(costs*variable,[P&L Lines],[P & L Layout], [Row No]) -

     aggr(personal costs*variable,[P&L Lines],[P & L Layout], [Row No])

)

Simulation now works perfectly.......................