topic Re: SUM of a value within a max date range in App Development

SUM of a value within a max date range

McCloudPT — Sat, 16 Nov 2024 20:23:05 GMT

Hello all

I'm currently struggling to get a final SUM value of a given max date, when we select a date range with Date Picker.

I have values per day that I want to show in a Map, but the sum adds the values from all days in the date range.

I need to show the sum of one day only. I can choose the max day of a range with: "Date(Max(Floor(DateDay), 1)) " but the final expression does not provide me the values I need.

Not working: =sum({<TvPlatform = {'VTV'},DateDay={'=$(Date(Max(Floor(DateDay),1)))'}>} Value)

Re: SUM of a value within a max date range

mahaveerbiraj — Fri, 26 Jul 2019 13:30:44 GMT

Hi ,

Refer below example,

Create variable in qliksense Front call vMaxDate= =date(max(OrderDate), 'YYYY-MM-DD') as i shown in attached imag,

and use that variable in SetAnalysis as shown below.

=sum({<OrderDate={'$(vMaxDate)'} >} [Sales Amount])

Re: SUM of a value within a max date range

sunny_talwar — Fri, 26 Jul 2019 18:19:26 GMT

You can also try this

=Sum({<TvPlatform = {'VTV'}, DateDay = {"$(=Date(Max(Floor(DateDay))))"}>} Value)

Re: SUM of a value within a max date range

McCloudPT — Mon, 29 Jul 2019 10:03:33 GMT

Hello Sunny_talwar, thanks for your answer!

Just to add more info, i'm using a map with pie charts were the data measure is the sum of the value filtered by the TvPlatform.

The solution presented gives zero costumers in the pie charts with the formula: Sum({<TvPlatform = {'VTV'}, DateDay = {"$(=Date(Max(Floor(DateDay))))"}>}Value) .

With the =SUM({<$(DateDay = {"$(=$max(DateDay)-1)")},TvPlatform = {'VTV'}>}Value)
I get the total sum of all days, making the pie charts assume the total costumers in it's size along the map.

Re: SUM of a value within a max date range

McCloudPT — Mon, 29 Jul 2019 10:02:46 GMT

Hello Mahaveerbiraj, thank you for your help!

I did as requested and the result is also zero per pie chart in the map 😕

Re: SUM of a value within a max date range

sunny_talwar — Mon, 29 Jul 2019 11:27:55 GMT

What does your DateDay field format look like? Is it DD/MM/YYYY or MM/DD/YYYY or D-MMM-YYYY or something else? Also, is it only date or is it a timestamp?

Re: SUM of a value within a max date range

McCloudPT — Mon, 29 Jul 2019 13:23:55 GMT

Hello

It's defined like this:

Date#(Date(StartDate,'DD-MM-YYYY'),'DD-MM-YYYY') as DateDay

It's not a timestamp.

Re: SUM of a value within a max date range

sunny_talwar — Mon, 29 Jul 2019 13:52:27 GMT

Try this

Date(Floor(StartDate),'DD-MM-YYYY') as DateDay

and then this

=Sum({<TvPlatform = {'VTV'}, DateDay = {"$(=Date(Max(DateDay), 'DD-MM-YYYY'))"}>} Value)

Re: SUM of a value within a max date range

McCloudPT — Mon, 29 Jul 2019 15:34:28 GMT

Hello Sunny_talwar

Still no luck! the sum is always zero, no mater what the date is.

Re: SUM of a value within a max date range

sunny_talwar — Mon, 29 Jul 2019 16:03:35 GMT

Would you be able to share a sample where we can see the issue?

Re: SUM of a value within a max date range

McCloudPT — Mon, 05 Aug 2019 15:09:56 GMT

Since the set analysis was not cooperating, one of my colleagues found a weird but working solution:

=Sum({<TvPlatform = {'VTV'}>}Value * if(DateDay=RangeMin('$(=date(max(aggr(nodistinct max(DateDay), Distrito))))',Today()-1),1,0))

Every date but the max one is discarded by the * if expression!