topic Re: Issue with getting result of set analysis in QlikView

Issue with getting result of set analysis

Wed, 24 Feb 2016 12:59:41 GMT

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

Re: Issue with getting result of set analysis

settu_periasamy — Wed, 24 Feb 2016 15:00:06 GMT

Try this

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

Re: Issue with getting result of set analysis

sunny_talwar — Wed, 24 Feb 2016 15:24:40 GMT

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)

Re: Issue with getting result of set analysis

Wed, 24 Feb 2016 16:41:56 GMT

thanks for reply sunny.

Did't work well as expected.

Thanks

Kumar

Re: Issue with getting result of set analysis

Thu, 25 Feb 2016 15:24:15 GMT

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

Re: Issue with getting result of set analysis

settu_periasamy — Thu, 25 Feb 2016 16:45:29 GMT

Can you share your expression?

It may be like

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

Re: Issue with getting result of set analysis

Thu, 25 Feb 2016 21:52:13 GMT

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

Thanks

Re: Issue with getting result of set analysis

sunny_talwar — Thu, 25 Feb 2016 23:31:00 GMT

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)

Re: Issue with getting result of set analysis

Fri, 26 Feb 2016 06:55:44 GMT

Suuny,

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

Re: Issue with getting result of set analysis

settu_periasamy — Fri, 26 Feb 2016 07:23:30 GMT

Hi Surendra,

Can you provide the sample to Test?

Re: Issue with getting result of set analysis

Fri, 26 Feb 2016 08:46:53 GMT

I did shared a test app with orginal if it did't help.

Please let me know i will provide a updated one.

Thanks

Re: Issue with getting result of set analysis

settu_periasamy — Fri, 26 Feb 2016 09:46:35 GMT

Hi,

Can you check the attachment, is that you are looking?