Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

kenphamvn
Contributor III

How to show item not sale in month.

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

ITemMonthSum(Sales)
ABCJAN- or 0 (Item not sale in JAN)
ABCFEB10
ABCMAR30
ABCAPR10
DEFJAN10
DEFFEB20
DEFMAR- or 0 ( Item Not Sale in MAR)
DEFAPR80

Regards

5 Replies

Re: How to show item not sale in month.

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
Contributor III

Re: How to show item not sale in month.

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

Highlighted

Re: How to show item not sale in month.

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
Honored Contributor III

Re: How to show item not sale in month.

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
Contributor III

Re: How to show item not sale in month.

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 

Community Browser