Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rakeshshah
New Contributor II

Stopping rangesum showing after a given date

Hi

I am using rangesum function to plot Forecast vs Actuals along the same date line. When i run the Actual sum range, it works by restricting the date based on the set analysis (see formula below). However I want to also show forecast numbers for the next couple of weeks after the actuals have ended (ie "today"). When I do this it shows the actuals as a flat line across those dates (see image). Is there a way to stop the actuals showing for dates "after today"

Actual: rangesum(above( sum({<[Date]={"<$(Today())"}>}[Actual_deployment]),0,rowno()))

Forecast vs Actuals.PNG

thanks in advance

Rakesh

1 Solution

Accepted Solutions
OmarBenSalem
Esteemed Contributor

Re: Stopping rangesum showing after a given date

try; if (sum([Actual_deployment])=0,null(),

rangesum(above( sum({<[Date]={"<$(Today())"}>}[Actual_deployment]),0,rowno())))


this will work

7 Replies
devarasu07
Honored Contributor II

Re: Stopping rangesum showing after a given date

Hi,

You can try like below,

Rangesum(above(sum({$<DATE = {'$(=Date(Today() ))'}>}[Actual_deployment]),0,rowno()))

or

Rangesum(above(sum({$<Date = {"=$(=Date(Today(), 'MM/DD/YYYY'))"}>}[Actual_deployment]),0,rowno()))

Thanks,

Deva

rakeshshah
New Contributor II

Re: Stopping rangesum showing after a given date

Hi Deva, thank you for your reply. This sort of works... as the only measure in the graph it will reduce the dates dimension to less than today (btw had to a '<' into your formula)... however when you add the forecast measure it adds the future dates in (as expected) and the line flattens across. Trying to remove the flattening line on future dates.

Thanks

Rakesh

devarasu07
Honored Contributor II

Re: Stopping rangesum showing after a given date

Hi,

after adding the less than Today. is it working?

Rangesum(above(sum({$<Date = {"<=$(=Date(Today(), 'MM/DD/YYYY'))"}>}[Actual_deployment]),0,rowno()))

rakeshshah
New Contributor II

Re: Stopping rangesum showing after a given date

the set analysis is working, but its not displaying how I want it to.

will demonstrate with images

How it is currently showing

Forecast vs Actuals.PNG

How I would like it to show

Forecast vs Actuals2.PNG

devarasu07
Honored Contributor II

Re: Stopping rangesum showing after a given date

Hi,

can you send me forecast  expression? also would be able to share your mock data with app. thanks

may be u can try this

Less than today set condition apply only your  actual measure field

forecast just remove that set analysis part like below,

Rangesum(above(sum(Forecast_Amt]),0,rowno()))


Thanks,

Deva

OmarBenSalem
Esteemed Contributor

Re: Stopping rangesum showing after a given date

try; if (sum([Actual_deployment])=0,null(),

rangesum(above( sum({<[Date]={"<$(Today())"}>}[Actual_deployment]),0,rowno())))


this will work

rakeshshah
New Contributor II

Re: Stopping rangesum showing after a given date

Awesome thanks Omar and Deva for your help.

The combination has resolved it - but namely the logic of if(sum)

Community Browser