Legend wrong

[0li5a3a]

hI all.

I need to display in a Bar Cart the total number of the tickets for the last 30 days.I have a field named Severity where I have something like this Severity = S1, S2, S3, S4, S5,etc.

I want to display only the data for the last 30 days for a specific customer and I want to display the days without tickets too.

like below.

Dimensions :

Day

and

= IF( Customer LIKE 'xxx', Severity)

Expressions:

count({< Date = {"<=$(=DATE(MAX(Date)))>=$(=DATE(MAX(Date)-30))"}>}TICKETID)

and

sum(1) I use this to display all the dates with no data so this is blank.

I the below chart the data is selected ok only the problem is with the legend  because I can see all the Severities, there suppose to be just the severities for the last 30 days but in may example are all for example S1 which is from 2 months ago.

Could someone please advise ?

[Anil_Babu_Samineni · Accepted solution]

The, You could mandatory to use Master Calendar to generate missing dates

Master:

LOAD TICKETID, Customer, Severity, OrderDate

FROM [Test samples xlsx.xlsx]

(ooxml, embedded labels, table is Sheet1);

Calendar_Table:

LOAD

    Date(OrderDate) AS OrderDate,

    Year(OrderDate) AS Year,

    'Q' & Ceil(Month(OrderDate) / 3) AS Quarter,   

    Month(OrderDate) As Month,

    Day(OrderDate) As Day,

    Week(OrderDate) As Week;

Load Date(MinDate + IterNo() -1 ) AS OrderDate While (MinDate + IterNo() - 1) <= Num(MaxDate);

Load

    Min(OrderDate) AS MinDate,

    Max(OrderDate) AS MaxDate

RESIDENT Master;

Note - Calendar_Table not mandatory to carry all fields. You can continue with OrderDate alone for this case. It's upon you

Then, Create tabular table and follow the same

Use dimension as Day and Calc. Dimension for Customer

Expression is same but from presentation tab you must Un-Check the Zero counts for X-Axis scale

HTH

Anil

[0li5a3a]

Thanks Anil, I would try now to implement all the above:)

Regards,

C