set analysis with variables issue

[#1]

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

[#2]

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

[nilesh_gangurde]

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

[#4]

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. 

[#5]

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.

[#6]

where should I do it?

[#7]

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)'?

[#8]

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.

[#9]

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);

[Anonymous]

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.