Value Based on the Date Selection compared to Previous Date - Qliksense

[sakthi266]

Hi All, 

     I need a script to find the value based on date selection and comparing the previous dates and with the unique ID Column. 

Eg. Attached for the reference.

  Please help ASAP as i am searching for the solutions for implementing in the client.  

Regards, 

Sakthivel.S

[Taoufiq_Zarra]

@sakthi266  can you elaborate more ? for exemple why 0 in :

5

28-Apr-19

TYPE1

200

0

[chrismarlow]

Hi,

This might be a start, not sure on zero vs nulls though (& as @Taoufiq_Zarra  says a bit more info on the requirement would help ...)

If(Only({<DATE=>}DATE)=Max({<DATE=>}TOTAL <ID>
If(isnull(GetFieldSelections(DATE)),
DATE,
If(DATE<=GetFieldSelections(DATE),DATE))),Sum({<DATE=>}Amount),Null())

EDIT - note this is not a script function - rather a chart, not sure you would do this in a script given you want this to change with date selection...

Cheers,

Chris.

[sakthi266]

Hi Tauofiq_Zarra, 

             As mentioned, we have multiple Rows with same id (which is 5) when we are in 5th-July-2020. So we have to take maximum date value. 

Regards,

Sakthivel.S

[sakthi266]

Hi, 

      This is working fine. but the problem is if i give the expression in KPI it won't work as it was a character. I want to sum up the value in KPI Chart 

[chrismarlow · Accepted solution]

Hi,

Sorry for the dealyed reply, this response did not hit my feed.

For a KPI you need to wrap an AGGR round the function depending on what dimesnions you aggregate over, so something like;

sum(aggr(
If(Only({<DATE=>}DATE)=Max({<DATE=>}TOTAL <ID>
If(isnull(GetFieldSelections(DATE)),
DATE,
If(DATE<=GetFieldSelections(DATE),DATE))),Sum({<DATE=>}Amount),Null())
,ID, DATE))

Cheers,

Chris.