topic Re: Exclude/include dimention a table in App Development

Exclude/include dimention a table

stekol61 — Mon, 18 Nov 2019 15:31:20 GMT

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

Re: Exclude/include dimention a table

Stoyan_Terziev — Mon, 18 Nov 2019 15:45:24 GMT

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.

Re: Exclude/include dimention a table

y_grynechko — Mon, 18 Nov 2019 15:45:28 GMT

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.

Re: Exclude/include dimention a table

stekol61 — Mon, 18 Nov 2019 16:17:45 GMT

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

Re: Exclude/include dimention a table

stekol61 — Mon, 18 Nov 2019 16:22:03 GMT

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

Re: Exclude/include dimention a table

stekol61 — Tue, 19 Nov 2019 13:16:36 GMT

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?

Re: Exclude/include dimention a table

Stoyan_Terziev — Tue, 19 Nov 2019 14:26:04 GMT

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

Re: Exclude/include dimention a table

stekol61 — Wed, 20 Nov 2019 10:49:05 GMT

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