Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
geogou1973
Creator
Creator

Accumulate calculation

Hello.

I have a problem with the accumulate calculation.

I have the following expression that calculates the net sales by sales_date

Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}SALES_NET) and i give the label

Net Sales Month to Date

Th following expression calculates the margin by date

(Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}SALES_NET)-

Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}COGS)) and i give the label

Margin Month to Date

in these two expression i check the option Accumute with step back 31 and works perfect.

I want to divide the margin with the net sales in order to have the margin % with the folowing expression

[Margin Month to Date]/[Net Sales Month to Date] but the numbers are not correct. the numbers are the same like the Net Sales and Margin without Accumulation.

How can i have the correct calculation when i divide the Accumulative Margin with the Net Sales ?

Thank you in advance.

1 Solution

Accepted Solutions
sunny_talwar

Please find attached the two options available to you

1)

Capture.PNG

2)

Capture.PNG

View solution in original post

7 Replies
sunny_talwar

I think you will have to use RangeSum(Above()) function to calculate the margin % here

May be this:

RangeSum(Above(Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}SALES_NET)-

Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}COGS), 0, 31))/

RangeSum(Above(Sum({1<SALES_YEAR={'$(=vYear)'},SALES_MONTH={'$(=vMonth)'},SALES_WEEK={'<$(maxweek)'},FORMAT={'HYPFL','SUPER','PROXI','CASHC','HYPMI'}-{'FRANC'}>}SALES_NET), 0, 31))

geogou1973
Creator
Creator
Author

Hello Sunny

This formula is doing the same like the accumulation when you check the option in the expression tab.

I don't want to accumulate the division. i want to divide correctly the amounts from the columns that a have done the accumulation.

Thank you for your answer

sunny_talwar

Is there a possibility to share a sample with expected output?

geogou1973
Creator
Creator
Author

The last column is the correct division for the column [Month to Date System Margin]/[Month to Date Net Sales]

The column before is incorrect. How can i have the results like the last column ?     

DateNet SalesSystem MarginSystem Margin %Month to Date Net SalesMonth to Date System MarginMonth to Date System Margin %
01/04/2016641.80012.5321,95%641.80012.5321,95%1,95%
02/04/2016861.77520.6272,39%1.503.57533.1594,16%2,21%
03/04/20167.3374406,00%1.510.91233.5996,38%2,22%
04/04/2016557.75318.3713,29%2.068.66551.9708,89%2,51%
05/04/2016483.11816.7033,46%2.551.78468.67311,59%2,69%
06/04/2016460.40814.3253,11%3.012.19182.99814,34%2,76%
07/04/2016467.94716.3153,49%3.480.13999.31317,19%2,85%
08/04/2016533.18216.0903,02%4.013.321115.40220,07%2,88%
09/04/2016749.15328.5093,81%4.762.474143.91123,09%3,02%
10/04/20167.6395367,02%4.770.113144.44726,12%3,03%
11/04/2016516.64219.2393,72%5.286.754163.68629,22%3,10%
12/04/2016435.31717.4034,00%5.722.071181.08932,38%3,16%
13/04/2016436.64513.7813,16%6.158.717194.87135,54%3,16%
14/04/2016481.0824.1750,87%6.639.799199.04638,54%3,00%
15/04/2016532.45015.3142,88%7.172.249214.35941,53%2,99%
16/04/2016754.25318.1672,41%7.926.502232.52744,46%2,93%
17/04/20168.4763534,16%7.934.978232.88047,40%2,93%
18/04/2016548.22311.7202,14%8.483.201244.60050,28%2,88%
19/04/2016499.7456.1121,22%8.982.946250.71253,07%2,79%
20/04/2016511.9401.2170,24%9.494.886251.92955,73%2,65%
21/04/2016661.970-36.386-5,50%10.156.856215.54357,85%2,12%
22/04/2016759.508-29.878-3,93%10.916.364185.66659,55%1,70%
23/04/20161.118.403-47.725-4,27%12.034.766137.94060,70%1,15%
24/04/2016474.060-34.363-7,25%12.508.826103.57761,52%0,83%
12.508.826103.5770,83%12.508.826103.5770,00%
sunny_talwar

Please find attached the two options available to you

1)

Capture.PNG

2)

Capture.PNG

geogou1973
Creator
Creator
Author

It is correct Sunny,

thank you very much for your help.

sunny_talwar

No Problem