Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
I think you have the wrong function, it should be monthsstart not monthstart
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...
maybe this:
=sum( {< Data = {"=Day(Data)=1"} >} TIR )
I hope this can helps.
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..
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.
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?
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.
@tresescoWOW! 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.
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.