topic Simple set analysis query.. in QlikView

Simple set analysis query..

Wed, 06 Jul 2011 13:12:01 GMT

Hello Experts,

I am having a simple Set Analysis expression query. It feels like I have done this before, but unable to do it now..I am trying to get the data for the maximum available month and tried the below expressions, none of this work. I have data till May in this case and June data comes in July... can anyone help with this?

if (sum({<month = month(today())>}reportValue)<>0,

num(sum({<month = 'max(=month)'>}reportValue), $(vNumberForm)),'')

By default I want my chart to show the maximum available data and change only when we select other dates.....

Thanks in Advance,

Mady

Simple set analysis query..

brenner_martina — Wed, 06 Jul 2011 13:19:15 GMT

Hi,

the correct set analysis syntax is like this:

if (sum({<month = {$(=Only(month(today())))}>} reportValue)<>0,

num(sum({<month = {"$(=max(month))"}>} reportValue), $(vNumberForm)),'')

Simple set analysis query..

Wed, 06 Jul 2011 13:29:10 GMT

Hello Martina,

I tried the solution provided by you, but it returns 0 and I am sure when I do a sum(reportValue) and select May for which I have data till, I get a number....

By default the chart should show the last available month data and change only when a different month or year is selected.....

Any ideas?

Thanks Again,

Simple set analysis query..

brenner_martina — Wed, 06 Jul 2011 13:31:45 GMT

Hi,

did you select a month? Your expression gives you a result only if is select 1 month.

Simple set analysis query..

Wed, 06 Jul 2011 13:35:39 GMT

Unfortunately, now matter if i select a month or not, it still shows me 0 and the problem is if I have to select a month and then it shows a number, then all I have to do is sum(reportValue) and select month May and get the exact number....but i want the chart to show the maximum month value by default..

Any more ideas please...

Simple set analysis query..

brenner_martina — Wed, 06 Jul 2011 13:45:34 GMT

Hi,

maybe you enter better something like this:

If(GetSelectedCount(month)=1,

num(sum({<month = {"$(=max(month))"}>} reportValue), $(vNumberForm)),

num(sum({<month = {"$(=Only(month(today())))"}>} reportValue), $(vNumberForm)))

Simple set analysis query..

Wed, 06 Jul 2011 13:53:39 GMT

Martina, I tried this but have the same issue, I did not understand how would it solve my problem, if I add GetSelectedCount(month)=1 in my set analysis expression. I think by adding this, we are saying if the selected month count is 1 then do the calculation...

I want my Pivot to check for the max month Data before making any selections and show it only for that month, however it should change only when the user selects a different month or year....

Cant we write something to show it?? I am sure most of us might have already done that.

Simple set analysis query..

brenner_martina — Wed, 06 Jul 2011 13:56:53 GMT

Hi,

pardon, I did not understand, what did you want to do! Could you please post an example?

Simple set analysis query..

Wed, 06 Jul 2011 14:00:58 GMT

Hi Try the following

sum({$<OffenePostenHistorie.BELDATUM = {'<=$(FDatum)'}>} OffenePostenHistorie.OBETRAG)

regards

Simple set analysis query..

Wed, 06 Jul 2011 14:15:07 GMT

Hello Martina,

I have attached the Pivot table as seen in the image here. I have the below expression as seen to get this data :

if (sum(reportValue)<>0,

num(sum(reportValue), $(vNumberForm)),'')

which is fine, But i want this chart to show by default the maximum available month data in my case its May 2011. I want the set analysis to check for the maximum month and year available and show by default, later on, when I click on April 2011 or any other selection, it should change. Is this possible??

Thank again,

Simple set analysis query..

brenner_martina — Wed, 06 Jul 2011 14:20:43 GMT

Hi,

but this is my expression doing, maybe you have to change the true- and false-value.

Simple set analysis query..

Wed, 06 Jul 2011 14:27:09 GMT

Martina, The number seen in the screenshot is for the whole years. If i select May 2011, the number should be 1,142,223 only... this is showing it as 18 million which is wrong and its because I am not selecting any date in the above screenshot... can i get only May 2011 data by default...its defaulting to all years data and your expression below to get the max month (May) data by default doesnt work

If(GetSelectedCount(month)=1,

num(sum({<month = {"$(=max(month))"}>} reportValue), $(vNumberForm)),

num(sum({<month = {"$(=Only(month(today())))"}>} reportValue), $(vNumberForm)))

Appreciate your help again, any more suggestions?

Thanks

Simple set analysis query..

brenner_martina — Wed, 06 Jul 2011 14:31:53 GMT

Hi,

pardon, I meant something like this:

If(GetSelectedCount(month)=1,

num(sum({<month = {"$(=Only(month))"}>} reportValue), $(vNumberForm)),

num(sum({<month = {"$(=Max(month))"}>} reportValue), $(vNumberForm)))

Simple set analysis query..

Wed, 06 Jul 2011 14:58:40 GMT

Hello Martina,

I appreciate your time and effort in trying to help me with this situation. I figured out a way for this issue and I am hiding columns in Pivot and using multiple expressions, which solved my problem.

Thanks for all the help,

Mady

Simple set analysis query..

brenner_martina — Wed, 06 Jul 2011 15:05:49 GMT

Hi, Mady,

not at all and greetings from Munich