Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tmumaw
Specialist II
Specialist II

Calculation problems - Confused

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

17 Replies
tmumaw
Specialist II
Specialist II
Author

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

Not applicable

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.

tmumaw
Specialist II
Specialist II
Author

Nope.  Same results.

Not applicable

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.

tmumaw
Specialist II
Specialist II
Author

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

Not applicable

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?

tmumaw
Specialist II
Specialist II
Author

Langaar9,

Check this out once.  I created another table ISEGTEMP.

Not applicable

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;