exclude dimension in expression

[stekol61]

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?

[tresesco]

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

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

[stekol61]

Hi!

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

[tresesco]

Could you share your sample app? Meanwhile try :

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

[MaxCld]

Hi,

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

[stekol61]

Hi!

This expression works if I select one month.

If i select more months it doesn't work

[sunny_talwar]

How about this

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

[stekol61]

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

[sunny_talwar]

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

[stekol61]

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.