Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
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.
Improvement:
If(Aggr(Count({<Month={"$(=Month(Max(Date)))"},Year={"$(=Max(Year))"}SaleID),Products)<Aggr(Count({<Month={"$(=AddMonths(Month(Max(Date))),-1)"},Year={"$(=Max(Year))"}SaleID),Products),Products
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.
Thanks,
Nani
hi,
I tried below expression and its giving the correct result
please try:
=aggr(If(Count({<Month={'Jan'}>}SaleID) >count({<Month={'Feb'}>}SaleID),Product),Product)
Hi,
Find the attachment.
Regards,
Kaushik Solanki
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)
Hi Kaushik,
Thanks for the response.
My requirement is to display the product in listbox under improvement and detorioration sections.
Can you please help me with that expression.
Thanks,
Nani
Hi Gysbert,
Thanks for the response. My actual requirement is to show the products under Improvement and deterioration sections
by comparing the data with previous months.
can you please help me with the expression to display Improvement and deterioration in separate list boxes.
Thanks,
Ravi
Then I guess you should do that in script instead of front end.
Also if you do in script you may loose flexibility of getting results for different months.
Regards,
Kaushik Solanki
Can you please suggest me a work around for this.
Thanks,
Nani
Hi,
I have made changes in chart and now it looks like listbox.
Regards,
Kaushik Solanki