Create a dimension based on if statement

[qvhjenvo]

Hi,

I want to create a dimension in a fact table

Customer Number	CY Sales	PY Sales
1	1000	20000
2	1000000	100000
3	2000	0
4	300000	0

What I want is to make the new dimension (CustNo_noSales) that shows customer who have [PY Sales] = 0

Customer Number	CustNo_noSales	CY Sales	PY Sales
1	null	1000	20000
2	null	1000000	100000
3	3	2000	0
4	4	300000	0

LOAD [Customer Number],

     [CY Sales],

     [PY Sales]

     aggr(if(sum({<[PY Sales]={0}>}[PY Sales]), CustNo_noSales),CustNo_noSales)

FROM

(ooxml, embedded labels, table is Ark1);

aggr(if(sum({<[PY Sales]={0}>}[PY Sales]), CustNo_noSales),CustNo_noSales)

This statement does not work.

I need help!

Regards

Loi

[sunny_talwar · Accepted solution]

Try this:

LOAD [Customer Number],

     [CY Sales],

     [PY Sales],

   If([PY Sales] = 0, [Customer Number]) as CustNo_noSales

FROM

(ooxml, embedded labels, table is Ark1);

[maxgro]

Also in the chart with a calculated dimension

=if(([PY Sales])=0, [Customer Number])

[qvhjenvo]

Thanks, Sunny.

It works.

[jagan]

Try like this

LOAD [Customer Number],

     [CY Sales],

     [PY Sales]

    if(sum([PY Sales]) = 0, [Customer Number]) AS CustNo_noSales)

FROM

(ooxml, embedded labels, table is Ark1)

Group By [Customer Number];

Hope this helps you.

Regards,

jagan.