SUM of a value within a max date range

[McCloudPT]

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)

[mahaveerbiraj]

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

[sunny_talwar]

You can also try this

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

[McCloudPT]

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.

[McCloudPT]

Hello Mahaveerbiraj, thank you for your help!

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

[sunny_talwar]

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?

[McCloudPT]

Hello

It's defined like this:

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

It's not a timestamp.

[sunny_talwar]

Try this

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

and then this 

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

[McCloudPT]

Hello Sunny_talwar

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

[sunny_talwar]

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