Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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);
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);
Also in the chart with a calculated dimension
=if(([PY Sales])=0, [Customer Number])
Thanks, Sunny.
It works.
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.