Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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
Thanks Anil, I would try now to implement all the above:)
Regards,
C