topic Re: Calculation problems - Confused in QlikView

Calculation problems - Confused

tmumaw — Thu, 24 May 2012 18:06:32 GMT

I’m really confused. I have 3 dims (Year, Plant and Material) and this works sum({<[Count_Date_Num] = {$(=max([Count_Date_Num]))}>} [Value book qty_WRTBM]). Now I want to take out material and only have 2 dims (Year and Plant) and it does not work. Any ideas?

Thanks
Thom

Calculation problems - Confused

Thu, 24 May 2012 18:33:50 GMT

What happens when you create another object with only the expression and then add in the dimensions?

Re: Calculation problems - Confused

Thu, 24 May 2012 18:46:17 GMT

I tested your expression with some dummy data. Everything seems to work and I can remove the dimensions. Could your object just be corrupted somehow?

Re: Calculation problems - Confused

tmumaw — Thu, 24 May 2012 19:53:39 GMT

Could you try multiple plants thanks

Re: Calculation problems - Confused

Thu, 24 May 2012 20:10:46 GMT

Like this? I am guessing at the structure of your data, obviously. Did you retry creating the object?

Re: Calculation problems - Confused

tmumaw — Fri, 25 May 2012 11:42:39 GMT

Yes and I get the same results. When I select a year and plant the correct dollar values shows up, however when I select all years and all plants everything goes to 0 dollars. Really confused.

Re: Calculation problems - Confused

Anonymous — Fri, 25 May 2012 12:24:31 GMT

Hard to say with this little information, but I'm guessing the dollar-sign expansion evaluates to NULL when things go wrong. These expressions can be debugged using the technique decribed here: http://community.qlik.com/message/175219#175219

Message was edited by: lf_fk_bkk

Re: Calculation problems - Confused

tmumaw — Fri, 25 May 2012 13:04:12 GMT

So what it looks like it's doing is setting the Max date based on all the dates in the QVW, not based on the dimensions I have selected. How can I make it based on dimensions? Any ideas. Thank for the debugging link it helped.

Re: Calculation problems - Confused

Anonymous — Fri, 25 May 2012 13:24:05 GMT

This might not work, but you could try to explicitly set the values which are not being set in your dollar sign expansion by using the "Posible values" function P. However, this means you have set analysis within set analysis, so --- generally speaking --- when things get this ugly there may well be a different, better solution to your problem, such as computing the required max during load time and creating a new field, or something similar. Once again, hard to say something concrete without looking at the actual application.

Re: Calculation problems - Confused

Fri, 25 May 2012 13:39:02 GMT

The behavior I have seen from $ expansion is that it does take into account the selections made on dimensional data. That being said, can you scramble your data in the QVW and attach it to this thread for review?

Re: Calculation problems - Confused

tmumaw — Fri, 25 May 2012 13:58:25 GMT

Let me know if this works. Thanks for your help.

Re: Calculation problems - Confused

Fri, 25 May 2012 14:23:53 GMT

I think the problem is with the expressions that do not include the $ expansion logic: the "Valued Stock" expression should be a calculated dimension. Likewise, because of the $ expansion the "Count Date" field will always indicate the max date; therefore, the "Max Count Date" field/expression is not needed. Try disabling them both initially to see if the behavior is as you desire.

Re: Calculation problems - Confused

tmumaw — Fri, 25 May 2012 14:31:07 GMT

Nope. Same results.

Re: Calculation problems - Confused

Fri, 25 May 2012 15:00:24 GMT

Ok, I have adapted the QVW. I added two objects: an adapted version of your original object with the fields removed or created as dimensions and a version of your object with the $ expansion removed entirely. This was the intitial problem: "Yes and I get the same results. When I select a year and plant the correct dollar values shows up, however when I select all years and all plants everything goes to 0 dollars. Really confused." When you select all years, the $ expansion is going to consider all dates in those years and find the maximum date therein. In the ORIGINAL OBJECT, due to the other expressions, the $ expansion will display 0 for the rows that represent the records for the non-max-date. Take a look at the behavior of the other objects as you drill-though plants and years. The $ expansion is tied to your selections but in the end it is still going to pin your results to one specific date.

Re: Calculation problems - Confused

tmumaw — Tue, 29 May 2012 17:20:09 GMT

Ok, still going crazy. Any ideas on how to do it through the script? Like set and indicator on the most current date.

Re: Calculation problems - Confused

Tue, 29 May 2012 18:07:45 GMT

It looks like you are already using some variables in your calendar build. Why not create another for the max date and then create another "if" statement setting an indicator when Date = vMaxDate?

Re: Calculation problems - Confused

tmumaw — Tue, 29 May 2012 19:28:07 GMT

Langaar9,

Check this out once. I created another table ISEGTEMP.

Re: Calculation problems - Confused

Tue, 29 May 2012 19:38:00 GMT

Yep, that was going to be my other suggestion. I was thinking you could use this data in a mapping so you could filter by the indicator you were creating. I think it would look something like this:

ISEGTEMP:
LOAD %MAT_PLT_KEY,
Max(Count_Date_Num) as [MaxDatum]
Resident ISEG_Physical_Inventory Group By %MAT_PLT_KEY

MAP_ISEG:
Mapping load
%MAT_PLT_KEY & '_' & MaxDatum as Map_ISEG_Key,
'Maximum' as Indicator_Code
resident ISEGTEMP;

drop table ISEGTEMP;

ISEG_Physical_Inventory2:
load *,
applymap('MAP_ISEG', %MAT_PLT_KEY & '_' & Count_Date_Num, 'Not Max') as Max_ISEG_Date_Indicator
resident ISEG_Physical_Inventory Group;

drop table ISEG_Physical_Inventory Group;