In one dimension, the amount of pull of the maximum sum value of another dimension.

[vladimir_f]

Hi!

Sorry for Google Translator

There is a Period (month, roughly speaking, 201405, 201406, 201407, etc.).

there name Products.

there are Sales

very simple. - only 3x fields.

two measurements (Period and Products ... moreover period should be skipped) and amount.Sales  .

without modifying the script loading, table need  calculate  the product last a significant amount of sales per month. you can  compare it with the remains of the end of the month (instead of simply "max" is "the SUM".. In some product this "remains" or "Balance" was in January. Someone in March. .itd.

I thought that it is easy to do, just setting in the measurement of a single field "Products", and write the expression:

sum ({<Period = { '$ (= max (Period))}>} Sales)

but it was not there. $ (= Max (Period)) is interpreted as the maximum period generally in the document. . accordingly, many products will be on zero balance on this very last month. and I need to on their last month .. tried to shove the expression just = max (period) .. months displayed normal (if for some reason, empty values ​​also found to be significant, then max (if (Sales> 0,Period)), but just trying to insert a summation formula - it does not work ..

so i need 2 rows (or two columns)  -       Period----LastSales

thank you in advance

I corrected the message and translated into English .. but my updated version the next day gone. What does it mean? I am outraged by this fact...

many details have been written

Сообщение отредактировано: Владимир Фасюра

[sunny_talwar · Accepted solution]

Can you try this:

FirstSortedValue(Aggr(Sum(Sales), Period, Product), -Aggr(Period, Period, Product))

[vladimir_f]

thanks for the answer.

But this method, I also tried.

Same result as sum ({<Period = { '$ (= max (Period))'}>} Sales)

Visible only those products for which the period is the maximum..

for example, sales are 10 products in the last month.

Here it will be shown on the data in your example..

But the data will not be displayed on products, whose sales were 3 months ago .. 4 months ago. etc

I tried a variety of methods.

I am satisfied with the period that is shown when I enter into a separate column expression =max(Period) ..  without "$"

for example.. "Apple"   last Sales at 201205  10000$

=max(Period)   will be ‘201205’

but when I try to put this condition in the expression sum ({<Period = { '$ (= max (Period))'}>} Sales) The result is 0$,

Because =$ (= max (Period))   ...         = 201412  (its max date of Data table)

in your example, the results are exactly the same +(

[sunny_talwar]

Set analysis is evaluated once per chart. When you do Max(Period) in the chart, it calculates the overall max and not max by your dimension. Would you be able to share you input file or some dummy data to show how this can work?

[vladimir_f]

1st - the problem is solved!!

now - explanation:

when I realized that I can not quickly solve the problem, I have prepared a dummy-file to find the right solutions on low-size data. and then all calculations are carried out exactly in it..

and at this moment I somehow incorrectly uploaded data to that dummy from test file..   so.. all cells uploaded as non-"null" values.

so FirstSortedValue(Aggr(Sum(Sales), Period, Product), -Aggr(Period, Period, Product))   not worked properly

today:

I'm was going to send you dummy-data, but decided to first basically check  my primary work-data file

And it worked!

As it turned out, I had already used the same expression, which you gave to me(in test-file) .. but I forgot that i need to use in test-file  the condition  if(Sales>0,Period)

so..  for corrupted data useful the following code

FirstSortedValue(Aggr(Sum(Sales), Period, Product), -Aggr(if(Sales>0,Period), Period, Product))

last question: i cant understand how to share non-commercial examples of qlickview apps(i mean without "attempt to restore")  =(..

so.. *csv   and *.xls  to share (in next tasks if needed)