I have an issue while comparing this month data with previous months data.
I have fields like product name ,Sale ID sold and Month. For each sale there is one SaleID associated.
i need to compare the products items sold for each product with last months products and need to display the products under Improvement and Deterioration sections.
If any product has got more no.of products sold when compared to previous month it should come under improvement else it should come under deterioration.
To achieve this i have taken List box and instead of field i took expression and in expression for improvement i have considered the below expression.
but the issue is all products doesnt have sales in all months. while comparing the products comparison happening if that product have saleID's in both months other wise it is not comparing the products.
Please find the attached app and kindly help me in comparing the products even though the product doesnt have sales.
In the sample app for improvement section ideally i should get A,B,D products but the above expression returning only A.
The example you posted does not contain a date field. The Month field has only text values which can't be used to calculate the previous month. If you have a date field you can create a numeric month field that can be used.
LOAD
SalesDate,
Date(MonthStart(SalesDate),'YYYYMM') as Month
Year(SalesDate)*12+Month(SalesDate) as _MonthNum
Product,
SaleID
FROM
....etc;
Select the month in the Month field. Then you can use these expressions:
Sales of the selected month: count(distinct SaleID)
Sales of the month before the selected month: count({<Month=, _MonthNum={$(=max(_MonthNum)-1)}>}distinct SaleID)
