topic Re: How to referance data from column dimension in pivot in QlikView

How to referance data from column dimension in pivot

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

Quick question. I have column MONTHNo. I need to get data from those column like 9, 10, 11

I got query for coverage which used some total. I need data from column in pivot to help me scope data each month so that I will get same number 223. my expression below. Please advise.

Sum( {<$(mvLevelItem) , $(mvMeasureTME) , $(mvCHANNEL_GT), $(mvViewPeriod_TY), MONTHNo = {"9"} >} Total CoverageCust )

Re: How to referance data from column dimension in pivot

sunny_talwar — Wed, 29 Nov 2017 19:22:39 GMT

How are you getting 223? I see 233, is that what we are trying to change?

Re: How to referance data from column dimension in pivot

Anonymous — Thu, 30 Nov 2017 03:47:08 GMT

Hi Sunny,

My false, I put wrong number. You're correct (233). I am very new for QLIK Sense.

Original Expression

Sum( {<$(mvLevelItem) , $(mvMeasureTME) , $(mvCHANNEL_GT), $(mvViewPeriod_TY), MONTHNo = {"9"} >} Total CoverageCust )

This is what I want to link with data from MonthNo column. if They show 9. I want to auto get 9, 10, 11 up to each column.

MONTHNo = {"9"}

Re: How to referance data from column dimension in pivot

sunny_talwar — Thu, 30 Nov 2017 16:54:22 GMT

But you are already getting 233 for coverage? Is that not what you want? What is the expected number you want to see for Coverage?

Re: How to referance data from column dimension in pivot

Anonymous — Fri, 01 Dec 2017 04:25:14 GMT

Let's me explain about the logic. Actually the coverage number will be difference for each month for example

Month 9 Coverage = 233

Month 10 Coverage = 245

Month 11 Coverage = 250

It's depend on month, but when I used sum total. This will get the full coverage. I tried to filter for month 9 by using MONTHNo = {"9"} but when the month change in pivot table It's still show data for month 9 which is 233.

so I would like to refer to month in column header which will change dynamically. for example

Month 9 in column= MONTHNo = {"9"} = 233

Month 10 in column = MONTHNo = {"10"} = 245

Month 11 in column = MONTHNo = {"11"} = 250

Please advise

Re: How to referance data from column dimension in pivot

sunny_talwar — Fri, 01 Dec 2017 13:09:32 GMT

Try this

Sum({<$(mvLevelItem) , $(mvMeasureTME) , $(mvCHANNEL_GT), $(mvViewPeriod_TY)>}TOTAL <MONTHNo> CoverageCust)

Re: How to referance data from column dimension in pivot

Anonymous — Sun, 03 Dec 2017 02:43:20 GMT

I've tried. It's still doesn't work. The results show all zero.

Re: How to referance data from column dimension in pivot

sunny_talwar — Sun, 03 Dec 2017 06:54:44 GMT

May be share a sample where we might be able to see the issue?

Re: How to referance data from column dimension in pivot

Anonymous — Mon, 04 Dec 2017 18:30:59 GMT

Please find sample in attached file. !

Re: How to referance data from column dimension in pivot

sunny_talwar — Mon, 04 Dec 2017 19:03:53 GMT

May be like attached

Re: How to referance data from column dimension in pivot

Anonymous — Tue, 05 Dec 2017 02:35:02 GMT

It's work perfectly. Thank you, but this is a bit difficult for me. Is it possible to explain your expression?

If(Column(1), Only({<$(mvLevelItem), $(mvMeasureTME), $(mvCHANNEL_GT)>}

Aggr(NODISTINCT Sum({<$(mvLevelItem), $(mvMeasureTME), $(mvCHANNEL_GT)>} CoverageCust), MONTHNo)))