Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Month to Date sales - day by day

Dears,

I need to calculate Month to Date sales - day by day, but i get the same value for sales as:

Month/Day12/112/212/312/412/5
Sales-15,608,809.37-16,142,777.27-16,368,495.52-15,187,632.97-14,702,194.26
Month to Date Sales-15,608,809.37-16,142,777.27-16,368,495.52-15,187,632.97-14,702,194.26

 

"Sales" is already a measure with sum of total amounts of sales this day.

I need it to be like this:

Month/Day12/112/212/312/412/5
Sales-15,608,809.37-16,142,777.27-16,368,495.52-15,187,632.97-14,702,194.26
Month to Date Sales-15,608,809.37-31,751,586.64-48,120,082.16-63,307,715.13-78,009,909.39
1 Solution

Accepted Solutions
Highlighted

Try this

RangeSum(Above(Sum(Sales), 0, RowNo()))

If this is in a pivot table with Date pivoted across the top... then try this

RangeSum(Before(Sum(Sales), 0, ColumnNo()))

View solution in original post

10 Replies
Highlighted

Try this

RangeSum(Above(Sum(Sales), 0, RowNo()))

If this is in a pivot table with Date pivoted across the top... then try this

RangeSum(Before(Sum(Sales), 0, ColumnNo()))

View solution in original post

Highlighted
Contributor III
Contributor III

Thanks, it worked well

Highlighted
Contributor III
Contributor III

But i need to reset calculation every start of month, not aggregating forever

Highlighted

May be this

RangeSum(Above(Sum(Sales), 0, Day(DateField)))

or

Aggr(RangeSum(Above(Sum(Sales), 0, RowNo())), MonthField, DateField)
Highlighted
Contributor III
Contributor III

Not working

 

Note: this model is what get me results:

RangeSum(Before(Sum(Sales), 0, ColumnNo()))

 

Highlighted

Then try this

RangeSum(Before(Sum(Sales), 0, Day(DateField)))

but this should remain the same....

Aggr(RangeSum(Above(Sum(Sales), 0, RowNo())), MonthField, DateField)

 

Highlighted
Contributor III
Contributor III

Dear Sunny,

both of them not working,

for clarification, your suggestion with:

RangeSum(Before(Sum(Sales), 0, ColumnNo())

 is working fine, but it will sum all values starting from first day in table to the end, even there are days for consecutive months.

i need to reset the sum every start of month.

thanks for your cooperation

Highlighted

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

Highlighted
Contributor III
Contributor III

Many Thanks Sunny, it's working fine now with this:

RangeSum(Before(Sum(Sales), 0, Day(max(DateField))))