topic Re: Comparison with previous month data issue in QlikView

Comparison with previous month data issue

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Comparison with previous month data issue

prajuds99 — Mon, 04 Apr 2016 12:00:59 GMT

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)

Re: Comparison with previous month data issue

kaushiknsolanki — Mon, 04 Apr 2016 12:02:40 GMT

Hi,

Find the attachment.

Regards,

Kaushik Solanki

Re: Comparison with previous month data issue

Gysbert_Wassenaar — Mon, 04 Apr 2016 12:06:09 GMT

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)

Re: Comparison with previous month data issue

Mon, 04 Apr 2016 12:15:32 GMT

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

Re: Comparison with previous month data issue

Mon, 04 Apr 2016 12:17:38 GMT

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

Re: Comparison with previous month data issue

kaushiknsolanki — Mon, 04 Apr 2016 12:21:46 GMT

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

Re: Comparison with previous month data issue

Mon, 04 Apr 2016 12:41:11 GMT

Can you please suggest me a work around for this.

Thanks,

Nani

Re: Comparison with previous month data issue

kaushiknsolanki — Mon, 04 Apr 2016 12:45:43 GMT

Hi,

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

Regards,

Kaushik Solanki