topic Show transaction even if value is zero in QlikView

Show transaction even if value is zero

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

I have the following sales data per day below. Sales do not occur everyday, hence i created a Calendar.

How do i create a chart table to show transactions for the day before even though the value is zero.

So for example, the max date is 13th July, then i want to sales for the 12th July.

I've untick the box suppress zero and in dimensions show all values, but i cant get it right.

Kind regards

Nayan

Date	Amount
21-May	700
24-May	900
25-May	1000
27-May	400
28-May	700
31-May	200
01-Jun	200
02-Jun	500
03-Jun	200
06-Jun	200
10-Jun	1000
11-Jun	300
12-Jun	400
15-Jun	700
16-Jun	700
17-Jun	200
21-Jun	900
22-Jun	900
23-Jun	700
24-Jun	500
25-Jun	1000
30-Jun	600
02-Jul	900
03-Jul	300
04-Jul	100
09-Jul	100
10-Jul	500
11-Jul	400
13-Jul	600

Re: Show transaction even if value is zero

lironbaram — Sun, 16 Jul 2017 10:55:49 GMT

in order to display dates with zero

you'll need to create a calendar that have this dates

your script should look something like this :

Data:

LOAD Date,

Amount

FROM

(ooxml, embedded labels, table is Sheet1);

Calendar:

load date(start+IterNo()-1) AS Date

While date(start+IterNo()-1)<=end;

load min(Date) as start,

max(Date) As end

resident Data;

Re: Show transaction even if value is zero

Frank_Hartmann — Sun, 16 Jul 2017 11:02:50 GMT

attached sample might be helpful

Re: Show transaction even if value is zero

effinty2112 — Sun, 16 Jul 2017 11:13:34 GMT

Hi Nayan,

This trick might work for you.With Suppress zero values unchecked as you have done already, replace the expression

sum(Amount)

with

sum(Amount) + Sum({1}0)

Good luck

Andrew

Re: Show transaction even if value is zero

Anonymous — Mon, 17 Jul 2017 01:29:37 GMT

Thank you for your reply. I have created a Calendar, the issue I'm having is on the front end to get the Sales for the previous day. So if I select a Sales manager and if he sold nothing the day before, i want to info to only show the date for yesterday, even though the value is zero.

Kind regards

Nayan

Re: Show transaction even if value is zero

Anonymous — Mon, 17 Jul 2017 01:31:19 GMT

Hi Frank

Thank you for your reply. I have similar result to your QV Model and as mentioned above to Liron, i only require the day before sales, even though the value is zero.

Kind regards

Nayan

Re: Show transaction even if value is zero

Anonymous — Mon, 17 Jul 2017 01:58:20 GMT

Hi Andrew

Thank you for your reply. Your solution is on the right track, however its showing all dates. In this screenshot i only want to show 2017/07/17.

Kind regards

Nayan

Please note that the actual screenshot of the model I'm working with and different to the sample data I've given

Re: Show transaction even if value is zero

effinty2112 — Mon, 17 Jul 2017 07:21:10 GMT

Hi Nayan,

Try:

=sum(Amount) + sum({<Date = {'$(=only(Date))'}>}0)

Cheers

Andrew

Re: Show transaction even if value is zero

Anonymous — Mon, 17 Jul 2017 08:55:27 GMT

Hi Andrew

Thank you. It works. However is there a way to show the date, in this case the 2017/07/17. Also want to add in to say "No transactions for processed.

Kind regards

Nayan