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: 
Not applicable

Issue with getting result of set analysis

I am trying to return a result using set analysis which should return records only for one but instead it is returing all the results.

Can some one please help with my query please.

thanks

=sum({$<[~Cmmdty_Commodity]={'Steel'},[Cmmdty_Period]={"=max([Cmmdty_Period])"},>}Cmmdty_Price)

It should return result for the month of the june 2015 which is the maximum period we got and the data is passing from data island and i believe the selections will not impact the result of it. while is used only instead of sum i am not getting any data.

Thanks in adva

1 Solution

Accepted Solutions
settu_periasamy
Master III
Master III

Try this

=sum({$<[~Cmmdty_Commodity]={'Steel'},[Cmmdty_Period]={"$(=max([Cmmdty_Period])) "}>}Cmmdty_Price)

View solution in original post

11 Replies
settu_periasamy
Master III
Master III

Try this

=sum({$<[~Cmmdty_Commodity]={'Steel'},[Cmmdty_Period]={"$(=max([Cmmdty_Period])) "}>}Cmmdty_Price)

sunny_talwar

I addition you may also require to add date formatting to Max([Cmmdty_Period]) in case it is formatted as a date field:

=Sum({$<[~Cmmdty_Commodity]={'Steel'}, [Cmmdty_Period]={"$(=Date(Max([Cmmdty_Period]), 'Cmmdty_PeriodFormatHere'))"}>} Cmmdty_Price)

Not applicable
Author

thanks for reply sunny.

Did't work well as expected.

Thanks

Kumar

Not applicable
Author

thanks Settu.

I was trying to calculcate the same way for previous month by using addmonths function on the max

(date) but it is return zero instead of values.

Can you please help me with that.

thanks

settu_periasamy
Master III
Master III

Can you share your expression?

It may be like

=sum({$<[~Cmmdty_Commodity]={'Steel'},[Cmmdty_Period]={"$(=Addmonths (max([Cmmdty_Period]),-1)) "}>}Cmmdty_Price)

Not applicable
Author

I did try that but it is returing zero instead of the previous month values.

Thanks

sunny_talwar

Are you making a selection in Cmmdty_Period field or another field? If you are making selection in another date field, then tell your expression to ignore them:


=Sum({$<[~Cmmdty_Commodity]={'Steel'},[Cmmdty_Period]={"$(=Addmonths (max([Cmmdty_Period]),-1)) "}, Date = , Month, MonthYear, Year, Quarter>}Cmmdty_Price)

Not applicable
Author

Suuny,

i am not making any selection to infulence the results and the data is coming from a data island.

settu_periasamy
Master III
Master III

Hi Surendra,

Can you provide the sample to Test?