Show transaction even if value is zero

[Anonymous]

Hi

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

[lironbaram]

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;

[Frank_Hartmann]

attached sample might be helpful

[effinty2112]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[effinty2112]

Hi Nayan,

Try:

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

Cheers

Andrew

[Anonymous]

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