How to show item not sale in month.

[kenphamvn]

Dear All

I want to show items not show in month in table and KPI (Count item not sale in month)

Here my sample data

SaleTBL:
load
*
Inline
[
Item ,Month,Sales
ABC,FEB,10
ABC,MAR,30
ABC,APR,10
DEF,JAN,10
DEF,FEB,20
DEF,APR,80
];

MonthTBL:
load
*
Inline
[
Month
JAN
FEB
MAR
APR
];

In table i want to show all items include items not sale in JAN (ABC) and MAR( DEF) like this

ITem	Month	Sum(Sales)
ABC	JAN	- or 0 (Item not sale in JAN)
ABC	FEB	10
ABC	MAR	30
ABC	APR	10
DEF	JAN	10
DEF	FEB	20
DEF	MAR	- or 0 ( Item Not Sale in MAR)
DEF	APR	80

Regards

[its_anandrjs]

Try with this

SaleTBL:

load  *  Inline

[

Item ,Month,Sales

ABC,FEB,10

ABC,MAR,30

ABC,APR,10

DEF,JAN,10

DEF,FEB,20

DEF,APR,80

];

Left Join(SaleTBL)

MonthTBL:

load  *  Inline

[

Month

JAN

FEB

MAR

APR

];

Noconcatenate

Finaltab:

Load

*,If( Sales = 0 Or isnull(Sales) = -1 OR Sales = '-', 0,Sales) as ReviseSales

Resident SaleTBL;

Drop table SaleTBL;

On UI Part

Dim:- Item ,Month

Expression :- Sum(ReviseSales)

[kenphamvn]

Hi

Thanks for your response
your solution working well but because my data is very large, i don't want to join two tables

i want to implement it on Chart

Regards

[its_anandrjs]

You have to create the Missing data in your main table that is SaleTBL because from this table you have to do calculation. Because in the Months you don't have continue months Jan,Feb,Mar........

[antoniotiman]

Hi,

You don't join two tables.

MonthTBL:
LOAD Min(Month) as MinMonth,Max(Month) as MaxMonth;
load Month(Date#(Month,'MMM')) as Month Inline [ 
Month 
JAN 
FEB 
MAR 
APR 
]; 
LET vMinMonth=Peek('MinMonth');
LET vMaxMonth=Peek('MaxMonth');

SaleTBL: 
load Item,Month(Date#(Month,'MMM')) as Month,Sales Inline [ 
Item ,Month,Sales 
ABC,FEB,10 
ABC,MAR,30 
ABC,APR,10 
DEF,JAN,10 
DEF,FEB,20 
DEF,APR,80 ];
Join LOAD DISTINCT Item,$(vMinMonth)+IterNo()-1 as Month
Resident SaleTBL
While $(vMinMonth)+IterNo()-1 <= $(vMaxMonth);

Regards,

Antonio

[kenphamvn]

Thanks Antonio

This is my sample data, in fact there are many data column (Dimension) in table SaleTBL (Date,Region, Province, District, SaleManager,Saleman, Store (200k Store)  ), and ~100 Items (~40 not sale by day)

Transaction data 1.5Mil/Day

I want to show Items not sale in month and group by each Dimension above

if join table as you solution, size app will be increase

Regards