Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

lakwinder
Contributor III

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)

14 Replies

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)

reddys310
Honored Contributor II

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)

lakwinder
Contributor III

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)

MVP
MVP

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)

lakwinder
Contributor III

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 SALESMARGIN
A 3673836.37%
B 33833.35%
C 3023329.93%
D 1032210.22%
E 2033220.13%

Re: Table measure not working line by line

For the sample above, this expression would work:

Sum(SALES) / Sum(TOTAL SALES)

you will need to adapt the above expression to your scenario.

lakwinder
Contributor III

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?

Community Browser