Sum value at start date

[alexandernatale]

Hello,

my goal is to do the sum of a value field (TIR) ​​only for those dates that are beginning of the month

=sum({<Data{'$(=monthstart(Data))'}>} TIR)

where am i wrong?

[Lisa_P]

I think you have the wrong function, it should be monthsstart not monthstart

[alexandernatale]

thanks @Lisa_P  for the reply!

what's the difference between MonthStart e MonthsStart in the practice?

https://help.qlik.com/it-IT/sense/April2020/Subsystems/Hub/Content/Sense_Hub/Scripting/DateAndTimeFu...

https://help.qlik.com/it-IT/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/Scripting/DateA...

[agigliotti]

maybe this:

=sum( {< Data = {"=Day(Data)=1"} >} TIR )

I hope this can helps.

[alexandernatale]

thanks for reply...
i think that your formula don't work..
Data it's format DD/MM/YY while Day(Data)=1 is format NN...

While I comparison two quantities there is problems..

[tresB]

Why don't you try and see what happens with the proposed solution above? I am saying this because - the solution by @agigliotti above should work. If not working try to share a sample app where we can see the issue.

[alexandernatale]

I humbly apologize for the hasty response. Indeed the code works very well. What I ask you is if you could integrate or modify the formula in such a way that it shows me the monthly deviations (therefore 31/1 MINUS  1/1) and so on for all the months. I attach sample data and what I would like to build graphically.

@agigliotti i don't understand what the "meaning" of the formula..

= sum ({<Data = {"= Day (Data) = 1"}>} TIR)

if you wanted to explain the formula in words what would you write? 
for example: i sum all the row of the TIR that have data = at the day of the data = 1?

[tresB]

Try like:

Pivot chart

Dim 1: Month 

Dim 2: Region

Exp :

Sum( if(day(Data)=Day(MonthEnd(Data)), Recovered))
-
Sum( if(Day(Data)=1, Recovered))

The Feb output doesn't match with your expected output (as per sample) because, Feb is 29-day month in 2020 and in your data you considered 28 days. Hope you can adjust the rest accordingly.

[alexandernatale]

@tresBWOW! that's exactly what I was looking for! Perfect!

Since you are so kind I take this opportunity only to ask you one last thing: if the last month was not "full" as for example May 2020 (today we are at 29/5) but you wanted to make the difference anyway? This problem would happen for any current month.

[tresB · Accepted solution]

This?

Sum( if(Day(Data)=( Aggr(NODISTINCT Day(Max(Data)), Month)), Recovered))
-
Sum( if(Day(Data)=1, Recovered))

Note, Month is a dimension created in the script here.