Exclude/include dimention a table

[stekol61]

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

[Stoyan_Terziev]

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.

[y_grynechko]

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. 

[stekol61]

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

[stekol61]

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

[stekol61]

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?

[Stoyan_Terziev]

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)

;

[stekol61]

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)'