topic Re: exclude dimension in expression in App Development

exclude dimension in expression

stekol61 — Thu, 21 Nov 2019 10:18:39 GMT

Hi!

I has a table as below with these calculations:

Start check: if ((year(winc_start_date) &'-'& month(winc_start_date)=Month),1,0)

Clear check: if ((year(winc_cleared_date) &'-'& month(winc_cleared_date)=Month),1,0)

The dimension 'Ticket' is needed to make this work.

How can I exclude the 'Ticket' dimension in the table and only present the result per month month in one row?

Re: exclude dimension in expression

tresB — Thu, 21 Nov 2019 10:24:31 GMT

It's grain mismatch issue. May be you just need one aggregation function like:

Sum(If(....)) - in both expressions

Re: exclude dimension in expression

stekol61 — Thu, 21 Nov 2019 10:36:50 GMT

Hi!

Added Sum(If(....)) , but it doesn't give the correct value

Re: exclude dimension in expression

tresB — Thu, 21 Nov 2019 10:42:35 GMT

Could you share your sample app? Meanwhile try :

Sum(Aggr( Sum(If(....)) , Ticket))

Re: exclude dimension in expression

MaxCld — Thu, 21 Nov 2019 11:27:12 GMT

Hi,

Or Sum(Aggr( Sum(DISTINCTIf(....)) , Ticket))

Re: exclude dimension in expression

stekol61 — Thu, 21 Nov 2019 11:52:52 GMT

Hi!

This expression works if I select one month.

If i select more months it doesn't work

Re: exclude dimension in expression

sunny_talwar — Thu, 21 Nov 2019 14:13:18 GMT

How about this

Sum(Aggr( If(Year(winc_start_date) & '-' & Month(winc_start_date) = Month, 1, 0), Ticket, Month))

Re: exclude dimension in expression

stekol61 — Mon, 25 Nov 2019 15:25:23 GMT

Hi!

This works OK.

But I have problem with another expression.

It should calculate 'Average(winc_response_time_bh)' (in minutes) per month with the same dimensions as previous.

'winc_response_time_bh' is e.g '05:12:20' (hh:mm:ss) for one ticket-

I have tried with this:

=if ((start_check=1) ,(Avg(Aggr(If (winc_response_time_bh>0, (Num#(Subfield(winc_response_time_bh,':',1))
+ Num#(Subfield(winc_response_time_bh,':',2)) /60),winc_response_time/60)
,winc_ticket,Month))))

But it gives the same vale for each month

Re: exclude dimension in expression

sunny_talwar — Mon, 25 Nov 2019 15:37:43 GMT

I think it would be easier to help if you are able to share some sample data.

Re: exclude dimension in expression

stekol61 — Tue, 26 Nov 2019 11:51:59 GMT

Hi!

Enclosed is the app with sample data.

Filters are preset and you only need to select 'Period', 2019-08, 2019-09 and 2019-10

In the sheet 'My new sheet' I have tested my calculations.