# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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:
*
Inline
[
Item ,Month,Sales
ABC,FEB,10
ABC,MAR,30
ABC,APR,10
DEF,JAN,10
DEF,FEB,20
DEF,APR,80
];

MonthTBL:
*
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
MVP

## Re: How to show item not sale in month.

Try with this

SaleTBL:

[

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:

[

Month

JAN

FEB

MAR

APR

];

Noconcatenate

Finaltab:

*,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)

Contributor III

## Re: How to show item not sale in month.

Hi

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

MVP

## 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........

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;
Month
JAN
FEB
MAR
APR
]

LET vMinMonth=Peek('MinMonth');
LET vMaxMonth=Peek('MaxMonth');

SaleTBL:
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

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