Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
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........
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
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