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

[Anonymous]

Hi,

In your set analysis, I think you should also consider the year.

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

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

[PrashantSangle]

Yes it is executing correctly. If you want nullify selection of year in your current expression then you have to modify it as

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

Regards,

[jyothish8807]

Try this:

sum ({$<Month_Name={"$(=monthname(Addmonths(Month_Name,-1)))"},P_Month=,P_Yr=>}Time)

Br,

KC

[sagar_vij99]

Hi Prashant,

I tried this solution but it will not work when i have select January 2017. It shows sum of all December Data data whereas it should show December 2016 data only.

So i changed my Months data and brought it to numbers instead of Text. Now my expression is:

=sum({$<P_Month={"$(=Num(Month(Month_Name-1)))"},P_Yr={"$(=Num(Year(Month_Name-1)))"}>}Time)

Still the problem remains same as i select 2018 it shows me 0 for previous month. I mean when year is changing it is creating issue else it is working fine when i am selecting months from same year.

[sagar_vij99]

Hi,

I tried this solution but it will not work when i have select January 2017. It shows sum of all December Data data only whereas it should show December 2016 data only.

So i changed my Months data and brought it to numbers instead of Text. Now my expression is:

=sum({$<P_Month={"$(=Num(Month(Month_Name-1)))"},P_Yr={"$(=Num(Year(Month_Name-1)))"}>}Time)

Still the problem remains same as i select 2018 it shows me 0 for previous month. I mean when year is changing it is creating issue else it is working fine when i am selecting months from same year.

[jyothish8807]

Give a try to my solution. Keep the expressions as simple you can

[sagar_vij99]

It is giving me 0 for all selections.

[jyothish8807]

Check the sample:

[sagar_vij99]

I guess you missed the sample..