take previous month data in pivot

[surajdhall]

Hi All,

When there is no data available in the current month, i want to bring from previous month in the pivot table (attached file)

Product B- High has no data in Feb. So take 86.20% from previous month.

Product C- Low has no data in the May. So take 100% from previous month.

I want to do this in this chart locally, is it possible?

[sunny_talwar]

Try this

If(Len(Trim(sum(Margin)/sum(Sales))) = 0, Before(sum(Margin)/sum(Sales)), sum(Margin)/sum(Sales))

[Clever_Anjos]

Please try this

Alt(sum(Margin)/sum(Sales),

before(sum(Margin)/sum(Sales))

)

[surajdhall]

Thanks Sunny/Clever.

I will get back after working on it.

[sunny_talwar]

Just changed the expression for %... now changed for T also

[surajdhall]

Thanks again.

Sorry, in my main initial application, i didn't mention the correct expression.

ie. I am doing average, inorder to get the totals as average at the bottom row of pivot table.

Avg(aggr(sum(Margin)/sum(Sales), Product,Status,MonthYear))

Could you help on using this expression. I updated the same in the app.

[sunny_talwar]

The problem is that the combination of Product, Status and MonthYear is completely missing and you will need to make sure that this combination exists from the script

Generating Missing Data In QlikView

[surajdhall]

Hi Sunny,

Sorry i didnt understand.

You mean i cannot pick previous month value using before() function, when am using aggr() like below-

Avg(aggr(sum(Margin)/sum(Sales), Product,Status,MonthYear))

Could you please explain a bit more.

So, if i don't want the avg in the total row, then i am good to go with your previous solution(ie. test1.qvw). Am i right?

Thanks!

[sunny_talwar]

See how there is no Feb for Product = B and Status = High... you will need to make sure that the MonthYear Feb Exits for this combination... may be with the Sales 0 and Margin 0

[surajdhall]

Hi Sunny,

I added the row as you expected.Please note that my main expression is-

Avg(aggr(sum(Margin)/sum(Sales),

     Product,Status,MonthYear))