topic Re: 100 % for all dimensions in QlikView

100 % for all dimensions

v_jaideep — Sat, 16 Nov 2024 01:28:24 GMT

Hello,

I have a requirement looks to be simple but not able to figure it out.Kindly help me what I am missing

With below expression data is for rolling 3 years data, I have a output calculated for 100% - for each year. Attached screenshot as 'Full %'

=Pick(Match(Year,$(V_LastYear2),$(V_LastYear),$(V_YEAR)),
(Count({<[Limit Profile_Include]={1},Year={"$(=$(V_LastYear2))"}>}R)/
Count(TOTAL{<[Limit Profile_Include]={1},Year={"$(=$(V_LastYear2))"}>}R)),
(Count({<[Limit Profile_Include]={1},Year={"$(=$(V_LastYear))"}>}R)/
Count(TOTAL{<[Limit Profile_Include]={1},Year={"$(=$(V_LastYear))"}>}R)),
(Count({<[Limit Profile_Include]={1},Year={"$(=$(V_YEAR))"}>}R)/
Count(TOTAL{<[Limit Profile_Include]={1},Year={"$(=$(V_YEAR))"}>}R)))

Now I have to show data for all years and have below expression but data for each year is not calculated for 100 %. Attached o/p file '%'

=(Count({<[Limit Profile_Include]={1}>}R)/
Count(TOTAL{<[Limit Profile_Include]={1}>}R))

Re: 100 % for all dimensions

Vegar — Mon, 20 Jan 2020 10:11:00 GMT

Your denominator is calculating the TOTAL over all data in your chart. Try to exclude Year in your totalin, like below:

=(Count({<[Limit Profile_Include]={1}>}R)/
Count(TOTAL <Year> {<[Limit Profile_Include]={1}>}R))

Re: 100 % for all dimensions

v_jaideep — Mon, 20 Jan 2020 12:31:24 GMT

Vegar,

does this expression show data for coming years (2021,2022,etc) from 2018 ?

Re: 100 % for all dimensions

Vegar — Mon, 20 Jan 2020 12:33:33 GMT

Yes, it should give you 100% per Year for all available Years in your chart.