topic Re: Count Last 3 Months Identical Sale in QlikView

Count Last 3 Months Identical Sale

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I am trying to count Last 3 Months Identical Sales, and using below expression, but its not returning any thing.

Could you please advise me about this?

=Count(If(
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))>0
And
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))
=
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-1)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-1)),'YYYYMM'))"}>} Sale))
And
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))
=
floor(Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-2)),'YYYYMM'))"}>} Sale))
,1))

Kind regards,

Farrukh

Re: Count Last 3 Months Identical Sale

sunny_talwar — Thu, 15 Dec 2016 17:13:57 GMT

You would need to Add Aggr between Count and if and use your dimensions

Re: Count Last 3 Months Identical Sale

nagarjuna_kotha — Fri, 16 Dec 2016 05:11:17 GMT

Can u please share sample data along with output .

It would better i guess

-Nagarjun

Re: Count Last 3 Months Identical Sale

Anonymous — Fri, 16 Dec 2016 12:14:57 GMT

Thanks Sunny.

Re: Count Last 3 Months Identical Sale

Anonymous — Mon, 19 Dec 2016 12:17:48 GMT

Hi Sunny,

Can you please see the attached sample data, as per your advised I used the dimension with Aggr function and it was working but due to some reason its just returning zero values.

I am trying to count Last Three month Identical sale. By displaying MonthYear Dimension for last 7 months.

Can you please advise me about this?

Kind regards,

Farrukh

Re: Count Last 3 Months Identical Sale

sunny_talwar — Mon, 19 Dec 2016 12:34:35 GMT

Your YearMonth field is not read as date here. It seems to be read as a text or number... Try this in the script:

LOAD SaleDate,

Date(MonthStart(Date#(YearMonth, 'YYYYMM')), 'YYYYMM') as YearMonth,

Region,

CityCode,

ProCode,

District,

Sale

FROM

(ooxml, embedded labels);

$(Include=F:\TestChartCalendar2.txt);

Re: Count Last 3 Months Identical Sale

Anonymous — Mon, 19 Dec 2016 12:48:29 GMT

Thanks Sunny,

I change the format in excel file and its sorted.

As you can see the I have a count of identical sale of last three month and Current month is selected Sep Year is 2016.

Could you please advise me how to display count of last 6 months.

Aug-2016

Jul-2016

Jun-2016

Re: Count Last 3 Months Identical Sale

sunny_talwar — Mon, 19 Dec 2016 12:52:27 GMT

May be this:

=Count({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-2)),'YYYYMM'))"}>} Aggr(If(

floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))>0

And

floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))

floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-1)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-1)),'YYYYMM'))"}>} Sale))

And

floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),0)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale))

floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-2)),'YYYYMM'))"}>} Sale))

, 1), Region, CityCode, ProCode, District))

Re: Count Last 3 Months Identical Sale

Anonymous — Mon, 19 Dec 2016 14:28:08 GMT

Hi Sunny,

Its returning a count just for one month, and other two months count are zero.

Farrukh

Re: Count Last 3 Months Identical Sale

sunny_talwar — Mon, 19 Dec 2016 14:32:23 GMT

Not sure what you required output is, but isn't this what you want?

Re: Count Last 3 Months Identical Sale

Anonymous — Mon, 19 Dec 2016 16:34:14 GMT

Hi Sunny,

I think I need to make some change at designing side.

is there any possibility that I can do something when I load the data

LOAD SaleDate,
YearMonth,
Region,
CityCode,
ProCode,
District,
Sale,

If Last month (201610) Sale (200) = This Month (201611) Sale (200) Then "YES" as Identical_Sale

Re: Count Last 3 Months Identical Sale

sunny_talwar — Mon, 19 Dec 2016 16:36:30 GMT

Hahahaha, I have no idea what you are trying to do my friend. I think we will need to take a step back and understand what exactly are you looking to get

Re: Count Last 3 Months Identical Sale

Anonymous — Mon, 19 Dec 2016 16:52:04 GMT

Hi Sunny,

Sample data is attached.

As you can see the below table

Sep-2016 is a count of identical sale of last three month (its mean Jul Aug & Sep where the sale is same)

Aug-2016 is a count of identical sale of last three month (its mean Jun Jul & Aug where the sale is same)

Jul-2016 is a count of identical sale of last three month (its mean May Jun & Jul where the sale is same)

What I am trying to do is that I want to display

MonthYear | CityCode | ProCode | Identical_Sale_Count

Sep-2016

Aug-2016

Jul-2016

Not in different expression. I want to display the count in one express.

Re: Count Last 3 Months Identical Sale

sunny_talwar — Tue, 20 Dec 2016 03:22:04 GMT

So you would want to see 6 for WSZ_4 and 38 for WSZ_2 and so on?

Re: Count Last 3 Months Identical Sale

Anonymous — Tue, 20 Dec 2016 09:09:28 GMT

Hi Sunny,

I want to see like this as in below table.

MonthYear	CityCode	ProCode	L3M Identical - Sale Count
Sep-16	City_8	WSZ_4	2
Sep-16	City_9	WSZ_2	12
Sep-16	City_11	WSZ_548	1
Sep-16	City_14	WSZ_140	1
Sep-16	City_15	WSZ_31	1
Sep-16	City_16	WSZ_462	4
Sep-16	City_16	WSZ_457	9
Sep-16	City_16	WSZ_463	2
Sep-16	City_16	WSZ_459	3
Sep-16	City_16	WSZ_458	27
Aug-16	City_8	WSZ_4	2
Aug-16	City_9	WSZ_2	13
Aug-16	City_11	WSZ_548	1
Aug-16	City_14	WSZ_140	1
Aug-16	City_15	WSZ_31	1
Aug-16	City_16	WSZ_462	4
Aug-16	City_16	WSZ_457	9
Aug-16	City_16	WSZ_463	2
Aug-16	City_16	WSZ_459	4
Aug-16	City_16	WSZ_458	28
Jul-16	City_8	WSZ_4	2
Jul-16	City_9	WSZ_2	13
Jul-16	City_11	WSZ_548	1
Jul-16	City_14	WSZ_140	1
Jul-16	City_15	WSZ_31	1
Jul-16	City_16	WSZ_462	4
Jul-16	City_16	WSZ_457	9
Jul-16	City_16	WSZ_463	2
Jul-16	City_16	WSZ_459	4
Jul-16	City_16	WSZ_458	29

Re: Count Last 3 Months Identical Sale

Anonymous — Tue, 20 Dec 2016 13:56:59 GMT

Hi Sunny,

Do you think that it could be sorted at expression level?

or do I need to make some changes at scripting level.

Farrukh

Re: Count Last 3 Months Identical Sale

sunny_talwar — Tue, 20 Dec 2016 14:02:35 GMT

I am checking it and will let you know

Re: Count Last 3 Months Identical Sale

Anonymous — Tue, 20 Dec 2016 14:03:46 GMT

Thanks Sunny

Re: Count Last 3 Months Identical Sale

sunny_talwar — Tue, 20 Dec 2016 16:15:08 GMT

Try this:

Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>}

Aggr(

If(

floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)) > 0 and

floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)) =

Above(floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-3)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-1)),'YYYYMM'))"}>} Sale))) and

floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-2)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),0)),'YYYYMM'))"}>} Sale)) =

Above(floor(Sum({$<Year=, Month=, YearMonth={">=$(=Date(MonthStart(AddMonths(Min(SaleDate),-4)),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate),-2)),'YYYYMM'))"}>} Sale)), 2), 1)

, Region, CityCode, ProCode, District, MonthYear))

Re: Count Last 3 Months Identical Sale

Anonymous — Tue, 20 Dec 2016 16:46:30 GMT

Hi Sunny,

Thanks a mil you have done a great help.

Could you please advise me how to manage this expression for last 12 Months? I tried to change it by -11 for last 12 months but its only returning the 3 months only.

Sum({$<Year=, Month=, [Year Month]={">=$(=Date(MonthStart(AddMonths(Min(Date), -11 )),'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(Date),0)),'YYYYMM'))"}>}

Farrukh