Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ebrahimaljefri
New Contributor III

Between function

hi qlikers

i want to use between function in qlik and my expresion as shown below :

sum(if(Floor(CreatedDate) = MakeDate(year(Today()),Month(Today())-1) and MakeDate(year(Today()),Month(Today())),1,0))

6 Replies
galax_allu
Valued Contributor

Re: Between function

Hi

1)which measure are going to sum ?(sales , patents ?

2)Are you looking for sum between  current month and previous month ?

shraddha_g
Honored Contributor III

Re: Between function

Give some more details

Re: Between function

Use variables in the qlik and use them in your SET expression to calculated the dates between

Ex:-

Let vFrDate =MakeDate(year(Today()),Month(Today())-1);

Let vToDate =MakeDate(year(Today()),Month(Today()));


Then use SUM expression to SUM your metric fields but need to check other field selections as well


=Sum({<DateField={">=$(=vFrDate) <=$(=vToDate)"} >} Value)

pathiqvd
Contributor III

Re: Between function

Hi,

Try this,

In script make date field format like date(createdate,'DD-MM-YYYY') as createdate

in Expr:

sum(if(createdate>=date(addyears(today(),-1)),'DD-MM-YYYY') and createdate()<=date(today(),'DD-MM-YYYY'),1,0))

Regards,

Re: Between function

If you are using this in the load script then try this ways

Load

...

..

Sum(if( CreatedDate >= MakeDate(year(Today()),Month(Today())-1) and

             CreatedDate <= MakeDate(year(Today()),Month(Today())),1,0))

From Source

...

...;

ebrahimaljefri
New Contributor III

Re: Between function

i want to sum created for previous month and i am writing that code in expression as below :

sum(if(Floor(CreatedDate)>= MakeDate(year(Today()),Month(Today())-1) and Floor(CreatedDate)< MakeDate(year(Today()),Month(Today())),1,0))

actually it works but does not return the accurate number

note i am not trying to build it in script .