Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear all,
I need to create an if function where I will be able to see the function of all cash applied per team member today.
The expression is as following:
Sum(if(
[Cash App Type Code]='CASH'
and [Cash App Count] = '1'
and [Cash App GL Date] = '$(today())'
,[Cash App Count]))
This if function doesn't give the results as per today due to this date filter: and [Cash App GL Date] = '$(today())'
Can you please help me to define the right expression?
Thanks
Ugur
In a load script you will have to precalculate Today() in a variable and then do a $-sign expansion inside the load statement. Like this:
vToday = Today(1);
LOAD
.....
Sum(if(
[Cash App Type Code]='CASH'
and [Cash App Count] = '1'
and [Cash App GL Date] = '$(vToday)'
,[Cash App Count])) AS SumToday,
.....
FROM ..... // or a following SQL SELECT if reading from a SQL source
GROUP BY
<dim1>,<dim2>.... // All the non-aggregation fields
;
or we can use today() directly in if else statement without $ and '
like
Sum(if(
[Cash App Type Code]='CASH'
and [Cash App Count] = '1'
and [Cash App GL Date] = Today()
,[Cash App Count])) as today_cash_cnt
Regards,
try like below
Sum({<[Cash App Type Code]='CASH', [Cash App Count] = 1, [Cash App GL Date] = today()>}[Cash App Count])
May be this:
Table:
Load
*
From Table.csv;
vDay = Today();
ABC:
Load
*,
If([Cash App Type Code]='CASH' and [Cash App Count] = 1 and [Cash App GL Date] = '$(vDay)', Sum([Cash App value]),0) as PerDayCash
Resident Table;
Drop table Table;
In expression you can use the AGGR for checking values.
Sum(AGGR(Sum([Cash App Type Code],[Cash App Count]),[Cash App GL Date],[Cash App value]))
OR
AGGR(Sum([Cash App Type Code],[Cash App Count]),[Cash App GL Date],[Cash App value])