Set Analysis not working for me..!

[sagar_vij99]

I have a table with some fields with sample data and i have created field using "MonthName(MakeDate(P_Yr, Month(Date#(P_Month,'MMMM'))))" as Month_Name in script.

P_Month     P_Yr     Time     Month_Name

January     2018         40      January 2018

January     2018         30      January 2018

January     2018         40      January 2018

December    2017      10      December 2017

December    2017      40      December 2017

November    2017      20      November 2017

November    2017      60      November 2017

You can consider this as the sample data bcz i have huge data but somewhat similar.

Now the problem is i am calculating sum of time for current selected month and for previous month. For previous month i am using below set expression :

sum ({$<P_Month={"$(=Date(Addmonths(Month_Name,-1),'MMMM'))"}>}Time)

When i am selecting only months from list box everything is working fine but as soon as i select 2018 from list box(P_Yr) it shows me data for January 2018 but it does not show for December 2017.

I tried to use Month_Name in set and tried below expression but it always shoes me 0 as value:

sum({$<Month_Name={"$(=MonthName(Month_Name-1))"}>}Time)

Can anyone please see what incorrect i am doing here. Any help will be appreciated..!!

Regards

Sagar

[sasiparupudi1]

Expression

=sum({<Month_Name={"$(=Date(addmonths(Month_Name,-1),'MMMM-YYYY'))"}>}Time)

In the script

Load

Date(MakeDate(P_Yr, Month(Date#(P_Month,'MMMM'))),'MMMM-YYYY') as Month_Name,

* Inline

[

P_Month,P_Yr,Time,Month_Name1

January,2018,    40, January 2018

January,2018,    30, January 2018

January,2018,    40, January 2018

December,2017, 10, December 2017

December,2017, 40, December 2017

November,2017, 20, November 2017

November,2017, 60, November 2017

];

[sagar_vij99]

Hi Sasidhar,

Thanks for the help.. It is working fine but my only concern is when i make any selection from filter P_Month, for previous month numbers appear as 0.

And in my dashboard P_Yr and P_Month are filter(used as list box).

Can you explain why it is happening and how we can resolve this.?

[jyothish8807]

i can see the sample

[qliksus]

Have you checked whether previous month variable returning the correct month value ? You can add the previuous month expression in a chart with label and see what value is passed in SET analysis . Can you try creating a yearmonth field and try the below expression ?

sum({<Yearmonth={$(=date(addmonths(max(P_year)&max(P_Month),-1),'YYYYMM'))}>}time)

[anagharao]

try

=SUM({<P_Month=, P_Yr=, Month_Name={"$(=DATE(AddMonths(DATE#(Month_Name,'MMMM YYYY'),-1),'MMMM YYYY'))"}>}Time)

[sasiparupudi1]

May be try

=sum({1<Month_Name={"$(=Date(addmonths(Month_Name,-1),'MMMM-YYYY'))"}>}Time)