Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
PFA dummy application.
Thanks
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