topic Re: set analysis with variables issue in QlikView

set analysis with variables issue

Tue, 17 Dec 2013 12:48:12 GMT

Hi all,

This is the expression in by text box:

=sum({<month={$(vMaxMonth)}>}Revenue)&' '&sum({<month={$(vPrevMonth)}>}Revenue)

And this is the values of the variables:

this expression gives me empty result.

I tried this way:

=sum({<month={'$(vMaxMonth)'}>}Revenue)&' '&sum({<month={'$(vPrevMonth)'}>}Revenue)

this one shows me total sum without any filter.

when I try regular list box filter each date gives me different number.

where am I wrong?

Thanks,

Boris

Re: set analysis with variables issue

Tue, 17 Dec 2013 12:57:17 GMT

Maybe this works for you:

=sum({<month={"<=$(=vMaxMonth)"}>}Revenue)&' '&sum({<month={"<=$(=vPrevMonth)"}>}Revenue)

(This is to work without date selections)

Hope this helps.

Kind regards,

Nuno

Re: set analysis with variables issue

nilesh_gangurde — Tue, 17 Dec 2013 12:57:23 GMT

Hi,

You can check the Date format of Month Field. It should be same as the variables.

have same date format for both Month Field and Variables and use below mentioned expression.

=sum({<month={'$(vMaxMonth)'}>}Revenue)&' '&sum({<month={'$(vPrevMonth)'}>}Revenue)

hope this will help.

-Nilesh

Re: set analysis with variables issue

Tue, 17 Dec 2013 13:57:59 GMT

Nuno, it didn't help , still I get the total sum.

Nilesh, the variables are set using the month field with a peek function in the script.

on the left is a list of the month field and the right a text object with the variable vMaxMonth.

Re: set analysis with variables issue

Tue, 17 Dec 2013 14:08:43 GMT

What if you replace vMaxMonth for =max(all month) and vPrevMonth for =max(all month,2)?

This will be an automation for you and force the dates to take the number form.

Re: set analysis with variables issue

Tue, 17 Dec 2013 15:00:44 GMT

where should I do it?

Re: set analysis with variables issue

Tue, 17 Dec 2013 15:05:01 GMT

You go to Settings, then Variable Overview and change the value of vMaxMonth from '12/1/2013' to '=max(all month)' without the quotes.

In the same menu, but for vPrevMonth you change '11/1/2013' into '=max(all month,2)'?

Re: set analysis with variables issue

Tue, 17 Dec 2013 15:11:19 GMT

all data disappeared where I used this variables.

I think I have an issue with the set analysis and the syntax I'm writing with.

Re: set analysis with variables issue

Tue, 17 Dec 2013 15:16:47 GMT

this is the load script:

Data_Table:

LOAD Month,

[Price plan],

lifecycle,

[Value segment],

Network,

[Usage type],

[active subscribers],

[Subscriber Count],

Revenue,

mou,

arpu

FROM

(ooxml, embedded labels, table is Sheet1);

MAX_Month:

Load date(max(Month)) as Max_Month

resident Data_Table;

let vMaxMonth=peek ('Max_Month',0,'MAX_Month');

let vPrevMonth=date(date(vMaxMonth)-31);

Re: set analysis with variables issue

Anonymous — Tue, 17 Dec 2013 15:49:04 GMT

Hi Boris Kacevich

I think you might need to redefine the variables

vMaxMonth =Max(ALL Month)

vPrevMonth =AddMonths(Max(ALL Month), -1)

And then the expression you need to use in the text object is as follows:

=sum({<Month= {'$(vMaxMonth)'}>}Revenue) & ' ' & sum({<Month= {'$(vPrevMonth)'}>}Revenue)

I think it should work.

Re: set analysis with variables issue

preminqlik — Tue, 17 Dec 2013 16:45:19 GMT

seems to be you are complicating that by caculating in backend . you can caclulate it in front end also ...any way try this ...

sum(if(Month=$(vMaxMonth),Revenue))&' '&sum(if(Month=$(vPrevMonth),Revenue))