show only dimensions which have a not null value of measure

[JMAROUF]

Hi,

can anyone tell me how to show only dimensions which have a not null value in measutre:

ex:

ID	MEASURE
046B	5000
8998	4000

9FFF	5000
G998	0

G100

0

i dont want to display  G998 and G100 because they have a null value, the formula is:

sum({<invest={"*"}-{'tiers'}>} invest_amount)

Any help please?

[abhijitnalekar]

Hi @JMAROUF ,

Please uncheck the below checkbox from the property of dimension ID 

[JMAROUF]

Thank you @abhijitnalekar ,

even i uncheck it, it shows nul values, i think this case concerns the dimension nul values not measures.

Regards.

[MEDHA07]

Hi,

Try this.

=sum({<MEASURE={">0"}>}MEASURE)

[JMAROUF]

Hi @MEDHA07 ,

thank you for the answer, the 0 is the values of the measure after executing formula, i can't guess this result before setitng up the formula.

Regards;

[MEDHA07]

The formula will work even if you have nulls or blanks or zeros in your data set.based on above set analysi condition it will calculate only not null values while doing sum.

[JMAROUF]

yes, but my goal is not to show nul result after calculating (not before), for example if i have a 0 in a sum() operation i don't want to show all the row including dimension value,

e.g:

DATA

ID	Value_1	Value_2
046B	5000	-5000
8998	4000	1000
9FFF	5000	0
G998	0	0
G100	0	10

Table chart:

ID	sum(Value_1 +Value_2)
046B	0
8998	5000
9FFF	5000
G998	0
G100	10

desired result

ID	sum(Value_1 +Value_2)
8998	5000
9FFF	5000
G100	10

046B and G998 shouldn't appear.

[MEDHA07]

---

@JMAROUF wrote:

yes, but my goal is not to show nul result after calculating (not before), for example if i have a 0 in a sum() operation i don't want to show all the row including dimension value,

e.g:

DATA

ID	Value_1	Value_2
046B	5000	-5000
8998	4000	1000
9FFF	5000	0
G998	0	0
G100	0	10

Table chart:

ID	sum(Value_1 +Value_2)
046B	0
8998	5000
9FFF	5000
G998	0
G100	10

desired result

ID	sum(Value_1 +Value_2)
8998	5000
9FFF	5000
G100	10

046B and G998 shouldn't appear.

---

---

@JMAROUF wrote:

yes, but my goal is not to show nul result after calculating (not before), for example if i have a 0 in a sum() operation i don't want to show all the row including dimension value,

e.g:

DATA

ID	Value_1	Value_2
046B	5000	-5000
8998	4000	1000
9FFF	5000	0
G998	0	0
G100	0	10

Table chart:

ID	sum(Value_1 +Value_2)
046B	0
8998	5000
9FFF	5000
G998	0
G100	10

desired result

ID	sum(Value_1 +Value_2)
8998	5000
9FFF	5000
G100	10

046B and G998 shouldn't appear.

---

Backend itself you can do addition for value and value 2 using preceding load or resident load store the values into new field.

Load your data and in measure expression give the s

Same condition which i refered earlier.

T1:

load*,

Sum(value1)+sum(value2) as MS_FLAG;

Load

Dim1,

Dim2,

Value1,

Value2

From.....;

[JMAROUF]

I am in a complicated star schema whit many filters, the formula above is just an example, i thought that i can hide dimension basing on the value measure which is my goal, for the backend treatment we aren't authorised to do such formulas, we have a data warehouse and we should just load dimensions and facts and apply  filters and formulas in the  front end script for some business reasons.

Regards.