Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi!
In my load script i have created two variable:
if ((year(inc_start_date) &'-'& month(inc_start_date)='2019-9'),1,0) as start_check,
if ((year(inc_cleared_date) &'-'& month(inc_cleared_date)='2019-9'),1,0) as clear_check,
In the table I created (see below) these two expression and the result is OK. Only the sum of start and clear without any dimension:
Count({<start_check = {"1"}>}winc_ticket)
Count({<clear_check = {"1"}>}winc_ticket)
In the load script the month is hard-coded but i want to be able to select one or several months and present the result per month in the table.
Below is the data used with more dimensions
Hello,
Please bear in mind as long as you've hard-coded the month in how you calculate the field, if you use the expression
Count({<start_check = {"1"}>}winc_ticket)
You will always get (the 0s may get suppressed):
Aug = 0
Sep = 117
Oct = 0
Nov =0
I think on your back-end you should create the dimension:
(year(inc_start_date) &'-'& month(inc_start_date) as TicketMonth
And then on the front-end create a bar chart with the Dimension [TicketMonth] and the measure
Count(winc_ticket)
Let me know if that's what you are trying to achieve.
Kind regards,
S.T.
Hey,
what do you mean by: the month is hard coded?
To see the stats by month you should be able to create filter pane with the use of this dimension:
Month(inc_start_date) or Month(inc_cleared_date).
After picking one or more it should filter out the table.
Hi!
I had created in the load script
Year(inc_start_date)&'-'& Month (inc_start_date)as Month,
Year(inc_cleared_date)&'-'& Month (inc_cleared_date)as Month_C
The reason is that start date (Create) and cleared date (Cleared) are calculated differently and this is causing the problems i think
Hi!
With hard-coded month I mean '2019-9' in the expression:
f ((year(inc_start_date) &'-'& month(inc_start_date)='2019-9'),1,0) as start_check,
The problem is that Month(inc_start_date) and Month(inc_cleared_date) are calculated differently and are not always the same
Hi!
I updated the calculations:
Year(inc_start_date)&'-'& Month (inc_start_date)as Month,
Year(inc_cleared_date)&'-'& Month (inc_cleared_date)as Month_C,
if ((year(inc_start_date) &'-'& month(inc_start_date)=Year(inc_start_date)&'-'& Month (inc_start_date)),1,0) as start_check,
if ((year(inc_cleared_date) &'-'& month(inc_cleared_date)=Year(inc_cleared_date)&'-'& Month (inc_cleared_date)),1,0) as clear_check,
This seems to work if I use two different tables. One for 'Month' and one for 'Month_C'
How can i combine this to one table or graph?
Hi,
This can get resolved on the back-end.
The below approach will help you combine the Month and Month_C dimensions into a single field but will also keep the start_check and clear_check as separate fields so you can combine them in a single table.
FactTable:
Load
Year(inc_start_date)&'-'& Month (inc_start_date)as Month,
if ((year(inc_start_date) &'-'& month(inc_start_date)=Year(inc_start_date)&'-'& Month (inc_start_date)),1,0) as start_check
FROM YourSource.qvd (qvd)
;
CONCATENATE(FactTable)
Load
Year(inc_cleared_date)&'-'& Month (inc_cleared_date)as Month,
if ((year(inc_cleared_date) &'-'& month(inc_cleared_date)=Year(inc_cleared_date)&'-'& Month (inc_cleared_date)),1,0) as clear_check
FROM YourSource.qvd (qvd)
;
Hi!
Tried this but the result is not OK in this table.
Calculation used: 'Count({<start_check = {"1"}>}winc_ticket)'
However, this table with more dimensions show the correct result. I can also see that the new 'Month' filter works as it should.
Calculation used: 'if ((year(winc_start_date) &'-'& month(winc_start_date)=Month),1,0)'