14 Replies Latest reply: Dec 4, 2015 11:51 AM by Sunny Talwar

# Table measure not working line by line

Hi Guys,

I'm doing a table with two dimensions and around 11 or 12 measures, all the measures are displaying the correct data per dimension apart from one, can anyone see what i'm doing wrong and why this one isn't working.  I test the numbers in a KPI with the set analysis filtered to just one value from the dimension and it's right, but when i take the filter off and drop the whole expression in a table, i hope to see all the correct numbers per row for the values in the dimensions, 9 other measures are working as they should apart from this one.

The TEAM={"*"} filter in the set analysis is not working in this measure, where it is in others.

Sorry guys i've pasted the expression wrong, this is the correct expression

I want to divide each row of the dimesion by everything in that dimension

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"},TEAM={"*"}>}Value)

/

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"}>}Value)

• ###### Re: Table measure not working line by line

Are you looking to disregard any selection made in TEAM field? or are you trying to exclude rows where TEAM is Null?

For second option, what you have should work.

For first option, try this: TEAM = or just TEAM

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"},STHA_DESC={"NHS Dorset CCG"}>}Value)

/

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"},TEAM>}Value)

• ###### Re: Table measure not working line by line

Hi Lak,

You do not have to specify TEAM={"*"} as your set modifier " 1 " it self refers to the entire data set i.e here TEAM={"*"} .

So try your expression removing it:

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"},STHA_DESC={"NHS Dorset CCG"}>}Value)

/

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"}>}Value)

• ###### Re: Table measure not working line by line

Sorry guys i've pasted the expression wrong, this is the correct expression

I want to divide each row of the dimesion by everything in that dimension

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"},STHA_DESC={"*"}>}Value)

/

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"}>}Value)

• ###### Re: Table measure not working line by line

May be this (with TOTAL)

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"},STHA_DESC={"*"}>}Value)

/

SUM(TOTAL {1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"},STHA_DESC={"*"}>}Value)

• ###### Re: Table measure not working line by line

Are you looking for the TOTAL qualifier? Do you want to ignore the dimension?

As always, context is important, so please tell us the dimensions used and some more details of your data model.

Also some sample lines of data and requested results always help to understand.

TOTAL qualifier (to ignore Dimension2, but consider Dimension1) may look like

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"}>} TOTAL<Dimension1> Value)

/

SUM({1<PRODUCT={"xxxx"}

,SID_MONTH={"201509"}>}Value)

• ###### Re: Table measure not working line by line

All i want to do is DIVIDE DIM 1 "A" SALES by total sales of A,B,C,D,E as a % as a measure

 DIM 1 SALES MARGIN A 36738 36.37% B 3383 3.35% C 30233 29.93% D 10322 10.22% E 20332 20.13%
• ###### Re: Table measure not working line by line

For the sample above, this expression would work:

Sum(SALES) / Sum(TOTAL SALES)

• ###### Re: Table measure not working line by line

sales is a measure by the way not dimension.

I've tried your way, no figures are displayed

• ###### Re: Table measure not working line by line

in your table have you tried DIM1 as dimension and Sum(SALES) / Sum(TOTAL SALES) as expression?

• ###### Re: Table measure not working line by line

Yes.

• ###### Re: Table measure not working line by line

I get the correct figure in a KPI  when i filter STHG_DESC={"one_value"}, but when i lose the STHG_DESC={"one_value"} in the expression in the table hoping the dimension field will sort out the values for each field.

• ###### Re: Table measure not working line by line

Check it out in QlikView, should work the same way in Qlik Sense

• ###### Re: Table measure not working line by line

I know it works in QV, it's working in other parts of the table, it's just this measure.  I'll leave it for a while and take a look again, it's something iv'e done a hundred times, i'm just not thinking straight at the moment.  But thanks