Comparison with previous month data issue

[#1]

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

[prajuds99]

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)

[kaushiknsolanki]

Hi,

Find the attachment.

Regards,

Kaushik Solanki

[Gysbert_Wassenaar]

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)

[#5]

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

[#6]

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

[kaushiknsolanki]

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

[#8]

Can you please suggest me a work around for this.

Thanks,

Nani

[kaushiknsolanki]

Hi,

I have made changes in chart and now it looks like listbox.

Regards,

Kaushik Solanki