Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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;
attached sample might be helpful
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
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
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
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
Hi Nayan,
Try:
=sum(Amount) + sum({<Date = {'$(=only(Date))'}>}0)
Cheers
Andrew
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