topic Re: take previous month data in pivot in QlikView

take previous month data in pivot

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

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?

Re: take previous month data in pivot

sunny_talwar — Mon, 25 Sep 2017 11:38:48 GMT

Try this

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

Re: take previous month data in pivot

Clever_Anjos — Mon, 25 Sep 2017 11:41:14 GMT

Please try this

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

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

)

Re: take previous month data in pivot

surajdhall — Mon, 25 Sep 2017 11:50:19 GMT

Thanks Sunny/Clever.

I will get back after working on it.

Re: take previous month data in pivot

sunny_talwar — Mon, 25 Sep 2017 11:51:32 GMT

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

Re: take previous month data in pivot

surajdhall — Mon, 25 Sep 2017 12:01:23 GMT

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.

Re: take previous month data in pivot

sunny_talwar — Mon, 25 Sep 2017 12:10:25 GMT

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

Re: take previous month data in pivot

surajdhall — Mon, 25 Sep 2017 12:29:04 GMT

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!

Re: take previous month data in pivot

sunny_talwar — Mon, 25 Sep 2017 12:31:36 GMT

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

Re: take previous month data in pivot

surajdhall — Mon, 25 Sep 2017 12:44:16 GMT

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))

Re: take previous month data in pivot

sunny_talwar — Mon, 25 Sep 2017 13:04:12 GMT

Check out the attached

Re: take previous month data in pivot

surajdhall — Mon, 25 Sep 2017 13:11:39 GMT

Thanks you so much Sunny

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

Just a final query. May i know what Rangesum is doing here. Why atleast sales 0 or margin 0 is required (as you mentioned in the comment).

Avg(aggr(If(RangeSum(sum(Margin)/sum(Sales), 0.1) = 0.1, Above(sum(Margin)/sum(Sales)), sum(Margin)/sum(Sales)), Product,Status,MonthYear))

Re: take previous month data in pivot

surajdhall — Wed, 27 Sep 2017 13:56:20 GMT

Hi Sunny,

The expression is taking previous month data only till next month, not to the further months.

Avg(aggr(If(RangeSum(sum(Margin)/sum(Sales), 0.1) = 0.1, Above(sum(Margin)/sum(Sales)), sum(Margin)/sum(Sales)), Product,Status,MonthYear))

Product D- High has no data for Mar, Apr, May. The data is carried only till next month(ie. from Feb to Mar), not to the further months.

Please suggest.

Re: take previous month data in pivot

sunny_talwar — Wed, 27 Sep 2017 14:55:37 GMT

This unfortunately may not work with your requirement to get the Average for the total row.... if you need the total and also the carrying forward, you will need to do this in the script

Re: take previous month data in pivot

surajdhall — Wed, 27 Sep 2017 15:07:18 GMT

Hi Sunny,

ok, I am fine without the total row.

I using your first solution and notice the same issue in % and T columns

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

Re: take previous month data in pivot

sunny_talwar — Wed, 27 Sep 2017 15:10:49 GMT

Not worrying about the Average, you can use this

If(RangeSum(sum(Margin)/sum(Sales), 0.1) = 0.1, Before([%]), sum(Margin)/sum(Sales))

Where [%] is your column name within the square brackets

Re: take previous month data in pivot

surajdhall — Wed, 27 Sep 2017 15:22:02 GMT

Thanks Sunny

The T column (arrows) seems not working, how can i adjust it.

Could you suggest, what is rangesum with = 0.1 condition in your expression.

Re: take previous month data in pivot

sunny_talwar — Wed, 27 Sep 2017 15:26:04 GMT

Check attached

Re: take previous month data in pivot

surajdhall — Wed, 27 Sep 2017 15:36:23 GMT

Thank you so much again..

Final query. Because i cannot have total in the chart. I want to have it in a text objects(for each month) under the chart.

Could you let me know how the aggr expression showing average for a month column, please.

Re: take previous month data in pivot

sunny_talwar — Wed, 27 Sep 2017 15:38:16 GMT

Aggr() isn't going to work because we your using column labels... unfortunately the only way to do this would be to do it in the script or an even more complex expression.

Please consider marking correct and helpful response once you get what you were looking for...