Set Analysis

[vanderson009]

I have below data,

I need to build set analysis as to show only those sales for last two month and whose quantity is greater than 100. I have highlighted expected output.

Thanks,

Villyee

[sunny_talwar]

May be like this

Dimension

MonthYear

Product

Expression

Sum({<MonthYear = {"$(='>=' & Date(MonthStart(Today(), -1), 'MMM-YYYY') & '<' & Date(MonthStart(Today(), 1), 'MMM-YYYY'))"}>}Aggr(If(Sum(Quantity) > 100, Sum(Quantity)), MonthYear, Product))

Sum({<MonthYear = {"$(='>=' & Date(MonthStart(Today(), -1), 'MMM-YYYY') & '<' & Date(MonthStart(Today(), 1), 'MMM-YYYY'))"}>}Aggr(If(Sum(Quantity) > 100, Sum(Sale)), MonthYear, Product))

Here I am assuming that MonthYear is created in the script using a format like this

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

[vanderson009]

Hi Sunny,

I tried expression that you suggested but my bad both are not working and modified little bit.

Can you please let me know why below expression is not working,

Sum({<MonthYear={">=$(=Date(MonthStart(Today(),-1),'MMM-YYYY'))<=$(=Date(MonthStart(Today()),'MMM-YYYY'))"}>} Aggr(If(Sum(Quantity) > 100, Sum(Sales)), MonthYear, Product))

Thanks,

Villyee

[vanderson009]

PFA dummy application.

Thanks

[sunny_talwar · Accepted solution]

Change the MonthName function to Date(MonthStart()) function in the script

LOAD *, Date(MonthStart(Date), 'MMM YYYY') as MY INLINE [

    Date, Product, Quantity, Sales

    1/1/2018, A, 80, 1

    1/1/2018, B, 110, 1

    1/1/2018, C, 90, 1

    2/1/2018, A, 80, 1

    2/1/2018, B, 70, 1

    3/1/2018, A, 100, 1

    3/1/2018, B, 110, 1

    3/1/2018, C, 90, 1

    4/1/2018, B, 80, 1

    5/1/2018, B, 90, 1

    5/1/2018, C, 70, 1

    6/1/2018, A, 80, 1

    6/1/2018, B, 110, 1

    6/1/2018, C, 120, 1

    6/1/2018, D, 115, 1

    7/1/2018, A, 100, 1

    7/1/2018, C, 90, 1

];

and reloaded the app to get this