Expression search in Set Analysis - limit to 'column'

Anonymous — Thu, 16 Nov 2017 16:05:58 GMT

Hi,

I'm trying to produce a pivot table chart in Qlikview like the attached to show 12 month rolling penetration figures for add on products. We already use AsOfPeriod in a Master Calendar so the structure itself is okay the problem comes in that we use expression searches in the expression

(

Sum({<[client_key]={"=Sum({<[Add On Product]={'XYZ'}, PeriodType = {'Rolling 12'}>}[GWP]) > 0"}, PeriodType = {'Rolling 12'}>} [ClientCount])-

Sum({<[client_key]={"=Sum({<[Add On Product]={'XYZ'}, PeriodType = {'Rolling 12'}>}[GWP]) < 0"}, PeriodType = {'Rolling 12'}>} [ClientCount])

)

(

Sum({<[client_key]={"=Sum({<[Add On Product]={'Potential XYZ'}, PeriodType = {'Rolling 12'}>}[GWP]) > 0"}, PeriodType = {'Rolling 12'}>} [ClientCount])-

Sum({<[client_key]={"=Sum({<[Add On Product]={'Potential XYZ'}, PeriodType = {'Rolling 12'}>}[GWP]) < 0"}, PeriodType = {'Rolling 12'}>} [ClientCount])

)

for which the period in the expression search should only relate to the 12 months of each AsOfPeriod but is covering the months from Oct 17 back to Feb 16 - I've thought of using Aggr by AsOfPeriod or Total by AsOfPeriod but not sure which of these would be the correct way to proceed.

Thanks, Robert

Re: Expression search in Set Analysis - limit to 'column'

sunny_talwar — Thu, 16 Nov 2017 16:15:54 GMT

I guess it would just need you to restrict your AsOfDate to 12 months using set analysis

topic Re: Expression search in Set Analysis - limit to 'column' in QlikView

Expression search in Set Analysis - limit to 'column'

Re: Expression search in Set Analysis - limit to 'column'