As you wanted, I left axis with exact day. If it is the look you want, please see below steps:
- Right click axis and Format
- From Fields, choose UniqueAction
- Choose Pane
- Under Special Values, for Marks choose 'Hide (Connect Line)'
Do the same thing for UniqueName.
Or see below pics:
Please let me know if that is what you want, also you may want to play with this options to get the exact result you want by selecting other options.
Thanks a lot for your quick answer! Unfortunately, this does not work as I would like to. The problem here is that on February 7 and February 8, there are no data which means that the line should go to zero. Wrong decisions can be made by the business if the chart connects the points ignoring the missing values.
I can make it work for the daily view if I use a discrete variable and "Show Value at Default". However, if I switch to the weekly view, it looks like the second image in the table in my original post.
Really, the only thing I would need is to change the formatting of the x-axis based on the parameter control. The only solution I currently found is to create different worksheet and switch between them. However, this becomes tremendously difficult to maintain.
Any other suggestion?
Thanks for your help,
Did you get a solution?
I know this is old, but one way to do this is just not to have missing dates. You can achieve this in SQL, or I think with some work in EXCEL by:
1) Get the values of all possible variables (for example: Create table division as select distinct division from my table
Create table state as select distinct state from my table
Create table date as select distinct date from my table
2)Create a Cartesian product.
CREATE TABLE PADDING_DUMMY_DATA
SELECT DISTINCT Division, STATE, DATE, 0 as REC_CNT
CROSS JOIN state
CROSS JOIN date
Note (depending on the SQL variant) you don't need to include a join condition. The cartesian product gives all possibility and initializes your "counting variable" to 0.
3) Finally: add this dummy data to your "real" data in a union. Again the syntax may be a bit dependent on your sql variant. This works because---assuming your real data looks something like
Division State Date Rec_cnt
Top TX 1/1/2020 10
--the union will only put in dummy data if there is no real data for the particular combination of Divisions, State, Date
CREATE TABLE PADDED_FINAL_OUT
AS SELECT * FROM (
SELECT * FROM Real_data
SELECT * FROM PADDING_DUMMY_DATA
Hope this helps someone