Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need some advice on complex Range sum or set analysis expression which can accumulate Column 1 previous 12 records net value to be displayed in column2.
Here i am attaching example.
In the below excel is exported straight table from qlikview
Sum of 4A(Net Elgbl $) 12 months value is 643,910.565.26 but my expression returns incorrect value.
Can some one advice whats the wrong with formula or advice me if it is aggregating incorrectly.
4A(12M Elgbl $) Expression is
Sample qvw attached.
Appreciate your help
See my previous post, applying only() with set expression around Ratio.
Have you made any selections when you exported out the table? I am seeing different numbers for the column 4A(12M Elgbl $), but the same numbers for 4A(Net Elgbl $)
Finding it hard to understand what exactly needs to be checked and resolved.
Best,
Sunny
I believe this issue is caused by the fact that your field AsOfPeriod shows a load order that is not chronologic (create a list box with sort order Load order for field AsOfPeriod).
The method using rangesum with an advanced aggregation needs a properly sorted field as aggr() dimension,because aggr() always uses load order for its dimension values.
Looking at your script, I think the issue arise from use of distinct on your master calendar, which unfortunately reorders the master calendar values.
Try a master calendar without DISTINCT:
T:
LOAD
// Year(Temp_Date) * 100 + Month(Temp_Date) as [Period],
Date(MakeDate(Year(Temp_Date),Month(Temp_Date)),'YYYYMM') as Period1,
AutoNumber(Year(Temp_Date) * 100 + Month(Temp_Date)) as [Period_Ref],
Year(Temp_Date) as [Year],
Month(Temp_Date) as [Month],
Date(Temp_Date, 'YYYY-MM') as [Year-Month],
Year(Temp_Date)&'Q' & Ceil(Month(Temp_Date) / 3) as [YYYY-QQ],
'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter]
;
LOAD
MonthStart($(vMinDate), IterNo() - 1) as Temp_Date
AUTOGENERATE (1)
WHILE monthstart($(vMinDate), IterNo() - 1) <= $(vMaxDate);
Thanks swuehl and Sunny,
Removed Distinct from MasterCalendar load but still it did not work;
The load order is still not correct, have a look at the list box ordered by load order.
You are using a binary load. Does this load contain already an AsOfPeriod field?
Hi Swuehl,
Thanks your help, Now i am halfway through.. I am able to get rolling 12 months numbers If i don't apply filter or if i select year 2020 still i need to able to see same number 4A(12M Elgbl $) : 643,910,800.77 instead of 39,337,623.26.
Appreciate if your help if we can fix this with set analysis but i did not succeeded.
Attached updated qvw.
sum( aggr( rangesum( above(
Sum({<PeriodType = {'Current'}, AsOfPeriod= ,Year = >} ProductSales)
-
Round(Num( ((Sum({<PeriodType = {'Rolling12'}, AsOfPeriod= ,Year = >}[In EGBL Units]) + (Sum({<PeriodType = {'Rolling12'}, AsOfPeriod= ,Year = >}[In EGBL Units]) * only({<AsOfPeriod=, Year=>} Ratio) )) / Sum({<PeriodType = {'Rolling12'} , AsOfPeriod= ,Year = >} ProductGrams)),'#.00000')
*
Sum({<PeriodType = {'Current'}, AsOfPeriod= ,Year = >} ProductGrams)
)
*
(Sum({<PeriodType = {'Current'}, AsOfPeriod= ,Year = >} ProductSales) / Sum({<PeriodType = {'Current'} , AsOfPeriod= ,Year = >} ProductGrams))
,0,12) ),NDC11,AsOfPeriod))
only({<AsOfPeriod=, Year=>} Ratio) Ratio is at NDC11 level and hence I don't need above statement.
I have my Range sum expression like this
sum( aggr( rangesum( above(
Expression to calculate current month Net sales
,0,12) ),NDC11,AsOfPeriod)) ===> Aggregating 12 months net sales.
Here problem i am facing is when i select Year 2020 then 2010 Jan Current Month Net Sales and 12 Months Net sales same.
I need set analysis to calculate 12 months aggregated net sales even though i selected sp0ecific year.
This is the table I get when selecting Year 2020.
NDC11 | AsOfPeriod | 1A Sales | 1A Units | WAC | 12M DS | 12M Units | 3C=3A+3B | 3E | 3G (In Elgbl $) | 3G (In Elgbl Units) | 4A(Net Elgbl $) | 4A(12M Elgbl $) | 4A(Net Elgbl Units) | 4A(12M Elgbl Units) | 6C=6A-6B | 6E | 7C=7A-7B | 7E | 8C=8A-8B | 8E | CM Concessions | Final AMP $ | Final AMP Price $ | 3F | 12WAC | 3G 12M (In Elgbl $) | Column(4) - [3G 12M (In Elgbl $)] |
50242006001 | 202001 | 56852725.275615 | 293052 | 194.00 | 933107434.84 | 4863425 | 88895 | 1498310 | 17515102.02 | 90283 | 39337623.26 | 643910800.77 | 202769.35 | 3365115.26 | 545504.89 | 9637080.89 | 92312.16 | 1834685.90 | 38.45 | 764.61 | 700877.02 | 38636746.24 | $187.088772 | 0.30808 | 191.86220158176 | 17529652.19 | 915577782.65582 |
50242006001 | 202002 | 59548347.568517 | 306947 | 194.00 | 918606913.34 | 4781031 | 93110 | 1471355 | 18325837.28 | 94462 | 41222510.29 | 634171109.48 | 212484.36 | 3309676.54 | 608803.35 | 9374968.30 | 103023.73 | 1770801.16 | 42.91 | 737.96 | 713588.26 | 40508922.04 | $187.285941 | 0.30775 | 192.13571756926 | 35822315.08 | 882784598.25802 |
50242006001 | 202003 | 72174139.327536 | 372027 | 194.00 | 901031905.78 | 4681168 | 112851 | 1438684 | 22181631.31 | 114337 | 49992508.02 | 622361078.73 | 257690.60 | 3242484.48 | 562293.61 | 9132879.89 | 95153.20 | 1711796.91 | 39.64 | 713.35 | 842024.16 | 49150483.86 | $187.466910 | 0.30733 | 192.48013298534 | 57955639.84 | 843076265.93473 |
50242006001 | 202004 | 60591404.135105 | 312323 | 194.00 | 886277391.26 | 4597332 | 94741 | 1411257 | 18599962.41 | 95875 | 41991441.73 | 612443600.40 | 216448.40 | 3186074.11 | 523514.79 | 8907487.21 | 88590.92 | 1656861.93 | 36.90 | 690.43 | 688979.60 | 41302462.13 | $187.635866 | 0.30697 | 192.78082906179 | 76487507.47 | 809789883.7931 |
50242006001 | 202005 | 62521389.785773 | 322272 | 194.00 | 871052909.08 | 4510824 | 97758 | 1382956 | 19168194.90 | 98804 | 43353194.89 | 602208363.93 | 223467.53 | 3127867.95 | 548336.78 | 8671407.74 | 92791.38 | 1599322.24 | 38.65 | 666.43 | 691552.33 | 42661642.56 | $187.812926 | 0.30659 | 193.10282004352 | 95559283.27 | 775493625.81144 |
50242006001 | 202006 | 68227293.875043 | 351683 | 194.00 | 854438991.45 | 4416422 | 106681 | 1352073 | 20887636.53 | 107667 | 47339657.34 | 591037938.85 | 244016.47 | 3064349.39 | 664598.38 | 8385273.31 | 112465.55 | 1529582.66 | 46.85 | 637.34 | 728977.10 | 46610680.24 | $188.027073 | 0.30615 | 193.46859036715 | 116309760.23 | 738129231.21234 |
50242006001 | 202007 | 61072008.883429 | 310148 | 196.91 | 839567445.65 | 4333169 | 94081 | 1324837 | 18672400.90 | 94826 | 42399607.99 | 581039504.00 | 215322.61 | 3008332.39 | 544041.56 | 8151043.09 | 92064.53 | 1472493.69 | 38.35 | 613.53 | 633721.86 | 41765886.13 | $191.025755 | 0.30574 | 193.75366773307 | 136571246.86 | 702996198.7963 |
50242006001 | 202008 | 68728532.076657 | 349031 | 196.91 | 822831472.32 | 4239479 | 105876 | 1294186 | 20980803.19 | 106549 | 47747728.89 | 569789373.09 | 242482.46 | 2945292.89 | 561370.62 | 7909352.06 | 94997.01 | 1413586.30 | 39.57 | 588.96 | 691365.04 | 47056363.85 | $191.209699 | 0.30527 | 194.08787485569 | 157340566.29 | 665490906.03085 |
50242006001 | 202009 | 66809876.201728 | 339288 | 196.91 | 806562707.76 | 4148404 | 102920 | 1264391 | 20362892.55 | 103411 | 46446983.65 | 558856627.71 | 235876.21 | 2884013.43 | 612603.12 | 7645603.53 | 103666.74 | 1349302.84 | 43.18 | 562.14 | 648867.10 | 45798116.56 | $191.410779 | 0.30479 | 194.42722175903 | 177455940.14 | 629106767.62242 |
50242006001 | 202010 | 58786557.812545 | 298542 | 196.91 | 792247688.45 | 4068267 | 90561 | 1238175 | 17891643.83 | 90861 | 40894913.98 | 549240490.37 | 207680.86 | 2830092.23 | 537316.69 | 7414268.62 | 90926.52 | 1292919.54 | 37.87 | 538.62 | 553029.81 | 40341884.17 | $191.586529 | 0.30435 | 194.73838215928 | 195091401.13 | 597156287.31721 |
50242006001 | 202011 | 65607942.905372 | 333184 | 196.91 | 776271604.72 | 3978830 | 101069 | 1208916 | 19934214.58 | 101234 | 45673728.32 | 538514449.90 | 231950.10 | 2769914.17 | 604679.42 | 7153931.54 | 102325.87 | 1229467.55 | 42.62 | 512.16 | 594685.82 | 45079042.50 | $191.784165 | 0.30384 | 195.10044866155 | 214696969.18 | 561574635.54754 |
50242006001 | 202012 | 60595801.555906 | 307730 | 196.91 | 761516019.40 | 3896227 | 93348 | 1181893 | 18381364.59 | 93348 | 42214436.96 | 528614335.32 | 214382.25 | 2714333.72 | 587798.93 | 6900862.13 | 99469.30 | 1167786.91 | 41.43 | 486.43 | 529008.26 | 41685428.70 | $191.976806 | 0.30334 | 195.44962087179 | 232729208.35 | 528786811.05275 |