Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
ugurarslan
Contributor

If function on date - cash applied per team member today

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

4 Replies
MVP
MVP

Re: If function on date - cash applied per team member today

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

;

Re: If function on date - cash applied per team member today

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,

Great dreamer's dreams never fulfilled, they are always transcended.
ChennaiahNallani
Contributor III

Re: If function on date - cash applied per team member today

try like below

Sum({<[Cash App Type Code]='CASH', [Cash App Count] = 1, [Cash App GL Date] = today()>}[Cash App Count])

Highlighted
balabhaskarqlik
Honored Contributor

Re: If function on date - cash applied per team member today

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