Display the missing date in a Bar Chart

[0li5a3a]

Hi all,

I have a new challenge today to display all the dates if I don't have data in a Bar Chart for the previous week.

I have a filter which contains all the YearWeeks and two charts one is for the current selections and one for the  previous  week.

For the current selection I can display the missing dates like in the chart below.

My problem is with the previous  week where the chart looks like below where you can see that the dates 23 and 24 are missing.

My dimension for both charts is the calendar Day and my expressions are:

Current Selections:

=count({$<Status = {'Approved', 'Scheduled'},WeekNumber = {'$(=Max(WeekNumber))'}>}TicketD)  and

=sum({<WeekNumber = {'$(=Max(WeekNumber))'}>}1) ( I use this to display the nulls ) and like you can see all works,

Previous Week:

=count({1<Status = {'Approved', 'Scheduled'},WeekNumber = {'$(=Max(WeekNumber)-1)'}>}TicketID)

I tried to use =sum({<WeekNumber = {'$(=Max(WeekNumber))'}>}1)  and Sum(1) but the chart looks wrong

Could someone please help me?

Regards,

C

[pradosh_thakur]

This didn't work for you?

[0li5a3a]

On the example yes but in my application not.... I'm trying now on it.  I  would mark your answer Correct Answer after integration:)

[0li5a3a]

This works just if you have data for 2017, if you have data in 2016 or 2015 the data is not displayed.

I added data for 2016 and 2015 in the excel files. Could you please help me with this?

Regards,

C

[pradosh_thakur]

Hi

Generally we don't use date as dimension and show them week wise. IF you want to show for multiple years you have to filter it first by year and then by week. Because week will be from 1-52 and will content different dates in different years. So add a year filter and make it always select one value or do an on open trigger.

if you want to compare two different weeks of two different year i will suggest you go for alternate states.

regards

Pradosh

[0li5a3a]

Hi,

Thanks you Pradosh I would use this. Now I tested the data and if you select the week 40 and year 2017 on the Previous Week  the days are not in asc order.

I used in the Sort Tab the function Max(OrderDate) but the date 1 is not displayed after the date 30. Please find below.

Regards,

C

[pradosh_thakur · Accepted solution]

Hi

PFA. it is sorted now as per your requirement. please mark answers helpful so that whoever visits in future will find it easy.

regards

Pradosh

[0li5a3a]

Thank you Pradosh, I appreciate your help