aggr Problem

[#1]

Hi,

attached my Question as QVW.

Thanks

[#2]

Hi shideh,

as far as I understand your question you should proof the contents of your vars. According to your selection the values of your vars are

 <table><tbody><tr><th>varDat = </th><td><=Jan-2010>=Jan-2010</td></tr></tbody></table>
 <table><tbody><tr><th>varDat2 = </th><td><=Dec-2009>=Dec-2009</td></tr></tbody></table>

which means varDat is exactly(!) Jan-2010 and varDat2 is exactly Dez-2009. And logical consistent your sums() have two different results. This is correct. Or am I completly wrong?

Regards, Roland

[#3]

Hi Roland,

Thanks .

It is correct. both formel are right, I like to build new (Dimension or Formel )? i don't no, which is correct ?

I have 2 tables (Article_KPM1 and Article_KPM2).
I would like a (formel or Deminsion) to add to this on this Diagramm with the following criteria:
for all Articlenr in Article_KPM1 on selected date in MonthYearFrom(varDat) that are not in Article_KPM2 on selected MonthYearTo (varDat2), ,Amount of Sales from Article_KPM1

user selects the two CalenderBox and get the first sum (sales) from the Article-KPM1 and for the second calendar are the sum (sales) from the second file (Article-KPM2)

varDat is the selected "MonthYearFrom"

varDat2 is the selected "MonthYearTo"

I am now at home and I have no QlikView, so I will write an example
It may be that the field names are different than in QVW.

[

for Example:
Article_KPM1

ArticleNr "111111" sales "5" varDat "Jan-2010"

ArticleNr 111112 sales 6 varDat "Jan-2010"

ArticleNr 111113 sales 4 varDat "Jan-2010"

Article_KPM2

ArticleNr "111111" sales "15" varDat2 "Dec-2009"

ArticleNr "111112" sales "1" varDat2 "Dec-2009"

result: sume(sales) =4
\]

I have created a small example as QVW, my QVW has several of Diagrame and tables.
Sorry for my bad Englich.
Thanks
Regards

[#4]

Hello again,

took some time looking into your app on sunday evening (TV wasn't very interresting ;-)). This is the dimension I used in a chart to get only these Articles with a sum() > 0 in the first timerange. Perhaps I used the wrong time-variable, sorry if so.But I think it points anyway in the right direction.

=if( aggr(sum({<Date_MonthYear= {"$(=varDat2)"}>} Sales1), %Material_ID1) > 0,%Material_ID1)

The filter above you can also use in an expression, but you need to think about the second var then. For ex.like the following:

= sum( if( aggr(sum({<Date_MonthYear= {"$(=varDat2)"}>} Sales1), %Material_ID1) > 0 and Date_MonthYear . . . , Sales2)

Note: expressions (=english; formel is german) are not syntax-checked.

RR

[#5]

Hello Roland,

Thank you for your efforts. I was not yesterday on Office
It is unfortunately not what I look for it. In the Article KPM1 is Article that is not included in Artikel_kPM2. For this article, I would like to Sum (Sales). "Jan2010" and Select MonthYearto "Feb 2010". exist Material_ID1 "111139" with Sales 8. would like no products number, just only sum (sales) of this article as expressions.

Select MonthYearFrom

In Article_KPM1

For this Material is not any entry's in the Arikel_KPM2.

I

Thank you
Regards
Shideh

[#6 · Accepted solution]

Hi Shideh,

now watch your chart , I added a third expression. Same as in the chart "SUM RK". This was my "development-chart".

RR

[#7]

Hi Roland,

Thank you very much. That works perfectly. Can you please explain me the -=P ? and the Expression.

Thanks again

Regards

[#8]

Hi Roland,

Thank you very much. That works perfectly. Can you please explain me the -=P ? and the Expression.

Thanks again

Regards

[#9]

Hi shideh,

glad to help you.

The term "Material_ID1 -= P(Material_ID2)" is the short form of "Material_ID1 = Material_ID1 - P(Material_ID2)" where the '-' (minus) is a Set Operator and the P() function returns the set (list) of all indirectly (implicit) selected Material_ID2s. The set-members are recognizeably in a listbox normally with a white background color. Please see help for further details.

RR