using two expressions as one expression in bar chart

[#1]

Hi All

i have two expressions in bar chart

=sum({<IsSameMonth={'1'}>}sales)

=sum({<IsRolling6={'1'} ,IsPr={'1'} >}sales)

i want  to convert these two expressions into a single expression is it possible

[sunny_talwar]

Are you trying to get this/

Script used:

a:

LOAD Country,

  Date(Date#(Date, 'MMMYYYY')) as Date,

  Value;

LOAD * INLINE [

    Country, Date, Value

    Argentina,Jul2016,650

    Japan,Jul2016,300

    America,Jul2016,200

    Argentina,Jun2016,550

    Japan,Jun2016,200

    America,Jun2016,100

    America,May2016,300

    Argentina,May2016,250

    Japan,May2016,150

    America,Apr2016,200

    Argentina,Apr2016,170

    Japan,Apr2016,210

];

Temp:

Load min(Date) as minDate,

    max(Date) as maxDate

Resident a;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD $(varMinDate) + Iterno()-1 As Num,

  Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

LOAD TempDate AS Date,

    week(TempDate) As Week,

    Year(TempDate) As Year,

    Month(TempDate) As Month,

    MonthName(TempDate) as MonthYear               

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Dimension

MonthYear

Country

Expression:

=If(Rank(Sum(TOTAL <Country>{<MonthYear = {"$(=MonthName(Max(MonthYear)))"}>} Value)) < 3, Sum({<MonthYear = {"$(=MonthName(Max(MonthYear)))", "$(=MonthName(AddMonths(Max(MonthYear), -1)))"}>}Value))

[#3]

yes sunny yes that is what i'm looking for the image looks good for me

can we do it in the same way for last 5 months

[#4]

my list box is as of month here i will select values in asofmonth so will that impact the expression

[sunny_talwar]

Do you need AsOfMonth or did you just create that to make the above work? We can make this work based on selection in Month field? Is that good enough?

[sunny_talwar]

Try this expression out:

=If(Rank(Sum(TOTAL <Country>{<MonthYear = {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}, Month>} Value)) < 3,

Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -5), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month>}Value))

Where MonthYear is created in the script like this:

MasterCalendar:

LOAD TempDate AS Date,

    week(TempDate) As Week,

    Year(TempDate) As Year,

    Month(TempDate) As Month,

    Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear               

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

[#7]

Hi Sunny actually i need asof month to show some rolling sums and averages

so will this impact the set analysis you prvided

[#8]

Hi Sunny can we convert my two expressions in the way u worte

sum({$<mydate={"=$(=max(mydate))"},IsSameMonth={'1'}>}sales) 

sum({$<AsOfMonth={"$(=date(AddMonths(max(AsOfMonth),-1),'YYYY MMM'))"}sales ) 

[sunny_talwar]

But why do you need 2 expressions John? I have used just one expression to get the requirement, is this not the right thing you were looking for?

[#10]

Yes sunny but its not working for me in my application

[sunny_talwar]

So unless you share you application, I am not sure what else might I be able to suggest. If you have confidentiality constraints when sharing your data, you can look here to see if this helps you:

Preparing examples for Upload - Reduction and Data Scrambling