topic aggr Problem in QlikView

aggr Problem

Fri, 07 Jan 2011 16:29:39 GMT

Hi,

attached my Question as QVW.

Thanks

aggr Problem

Fri, 07 Jan 2011 17:17:19 GMT

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

aggr Problem

Fri, 07 Jan 2011 21:29:50 GMT

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

aggr Problem

Mon, 10 Jan 2011 12:50:52 GMT

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.

aggr Problem

Tue, 11 Jan 2011 09:48:25 GMT

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.

Thank you
Regards
Shideh

aggr Problem

Tue, 11 Jan 2011 21:52:09 GMT

Hi Shideh,

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

aggr Problem

Wed, 12 Jan 2011 08:30:02 GMT

Hi Roland,

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

Thanks again

Regards

aggr Problem

Wed, 12 Jan 2011 10:55:13 GMT

Hi Roland,

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

Thanks again

Regards

aggr Problem

Wed, 12 Jan 2011 15:54:04 GMT

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.