Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Sum a range of Data

Hello !

I need to create an expression which sums a range of data.

Example :

Select day 19, it sums the day 1 +2+3...18+19

Select April, it sums January + february+march+april

Select 2012, it gives me sum of 2011.

Actually, i have a expression which sums only the previous data (previous year, or month or day, not the range).

Here it goes:

=   if(GetSelectedCount(Dia)>0, Sum( {1<Data={"$(vPreviousDay)"},Tipo={'Banco'}>} [Valor Debito]),

  if(GetSelectedCount(Mês)>0, Sum( {1<Mês={"$(vPreviousMonth)"},Tipo={'Banco'}>} [Valor Debito]),

  if(GetSelectedCount(Ano)>0, Sum( {1<Ano={"$(vPreviousYear)"},Tipo={'Banco'}>} [Valor Debito]),

  Sum ( {1<Data={'$(=Date(Min(Data)))'}>} if(Tipo='Banco',[Valor Debito]))

  )))

VARIABLES:

vPreviousDay

=Date(Max(Data)-1)

vPreviousMonth

=month(addmonths(max(Data) ,- 1))

vPreviousYear

=vMaxYear-1

Thanks !

5 Replies
evan_kurowski
Specialist
Specialist

Hello Morandi,

You want to start creating variations on the "range" set-analysis syntax:

{<Field={">= start <= end "}>}

If you have a field DataNum, which was purely a numeric representation of entries in the field Data (i.e. less the formatting strings), you could use the following:

Month to Date:

Sum({1<DataNum={">= $(=Num(MonthStart(Max(DataNum)))) <= $(=Max(DataNum))"},Tipo={'Banco'}>} [Valor Debito]),

Year to Date:

Sum({1<DataNum={">= $(=Num(YearStart(Max(DataNum)))) <= $(=Max(DataNum))"},Tipo={'Banco'}>} [Valor Debito]),

Anonymous
Not applicable
Author

Hello Evan !

Thanks for the answer !

I got the logic, thank you, but this analysis is correct?

Didn´t worked... I'm trying to see how correct it, but i'm not finding.

Regards,

Morandi

evan_kurowski
Specialist
Specialist

Hello Morandi, Maybe I would need to see exactly how you implemented it, but the expression construction doesn't look off to me.  What we can do is break out the components and test in stages:

First ensure the new field is formatted as a num:

(i.e.  add a line      Floor(Num([Data])) AS [DataNum]


Use the Floor() function to drop any potential timestamp encoded information from the field [Data].  We are interested in this case with working with whole integers (no decimal).

At the end of the modified script, open a list box of the field DataNum, do you see a list of integers?  For example, today 8/5/2014 is the integer 41856. Make a two-field table box with [Data] and [DataNum] side by side.  Make sure the dates and integer representations correlate properly.

Then break down the individual components of the expression in text boxes:

- Do you get an integer for =Num(MonthStart(Max(DataNum)))?

- If yes, does the integer correlate to the first day of the maximum possible month in selection?
- Do you get an integer for =Max(DataNum)?

- If yes, does the integer correlate to the maximum date in selection?
- Test the expression without the range segment of set analysis
- Does the expression Sum({1<Tipo={'Banco'}>} [Valor Debito]) yield a result?

- Now insert the date-range segment back in but with hardcoded values, today 8/5/2014 = 41856 and 1/1/2014 = 41640.  If you have data for this year, try the expression Sum({1<DataNum={">= 41640 <= 41856"},Tipo={'Banco'}>} [Valor Debito]).
- Does the expression with the hardcoded integers yield a result?  Based on the syntax, it should remain the same regardless of what other selections you make in other fields.  Using the "1" in the set-analysis is telling the expression to ignore all selections and then reapply conditions in the fields [DataNum] and [Tipo]
- Replace the hardcodes with the expressions that produce your starting and ending integer:
swap: 41640 with $(=Num(YearStart(Today())))
     - swap: 41856 with $(=Num(Today()))

- This should yield the same results as the hardcode test.

Anonymous
Not applicable
Author

Hello Evan !

Thanks for the answer !

That worked. Only one thing is remaining.

If i select january, i want the sum of december, but using the formula:

Floor(Num(MonthEnd(AddMonths(Max(Data),-1))))

It gives me the december in theory but came with 0, but in set analysis i'm not setting year, or it will give me the amount of the year, not of the selected month.

Can you help me?

Thanks

evan_kurowski
Specialist
Specialist

Wagner Morandi wrote:

If i select january, i want the sum of december, but using the formula:

Floor(Num(MonthEnd(AddMonths(Max(Data),-1))))

It gives me the december in theory but came with 0, but in set analysis i'm not setting year, or it will give me the amount of the year, not of the selected month.

Can you help me?

Thanks

One issue I can see is that you are using "MonthEnd" and not "MonthStart".

You jump back one month from any date in January, it will be December of the prior year, but then MonthEnd() pushes that date assignment to 12/31.  Meaning your expression is calculating the activity between 12/31 and 1/1 of the following year (which could very well tabulate zero activity).

For prior month use:

>= $(=Floor(Num(MonthStart(AddMonths(Max(Data),-1))))) < $(=Floor(Num(MonthStart(Max(Data)))))


Remember not to use "<=" but only "<" on the second portion, you don't want to include the first day of the current month in calculation. "Greater than or equal to the first day of the prior month, and less than the first day of the selected month"


That way if down the road, things get more sophisticated and you start adding timestamp level data, you won't have to adjust this expression.  Things that happened at 11:30p.m. on the last day of the prior month (i.e. have a timestamp value) will still calculate in the proper bucket.