topic Re: Difference between two months in QlikView

Difference between two months

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I'm trying to do a difference between two months in the straight table. My source is Excel sheet and the problem is the IDs which are new in the month of October (I filtered out - hyphen in Sep and removed - hyphen in Oct) which gives a total of 180 new IDs (customers) in Oct. All the new customers data has been squished all together in one sheet. Please provide assistance in tackling this issue in Qlikview.

Thanks in advance.

Re: Difference between two months

sunny_talwar — Fri, 01 Apr 2016 21:24:42 GMT

I am not sure I completely understand what you are looking to do. Can you elaborate a little more please?

Re: Difference between two months

Fri, 01 Apr 2016 21:57:55 GMT

The dollar amount sum(data) gives you different for all the respected months which means new customers (IDs) were added in the month, that's the only way the sum would increase if we had new customers come in but the count is stagnant for the second month even though the the sum increased about $60K. Is this possible in Qlik? I need to find out the number of new IDs which were added in respected months.

Thanks,

Re: Difference between two months

sunny_talwar — Fri, 01 Apr 2016 22:07:58 GMT

It seems that there are times when the data for Customer Internal ID changed from Sept to Oct

Re: Difference between two months

Fri, 01 Apr 2016 22:23:54 GMT

Hi Sunny,

The Excel sheet has been squished all together. For September filter out only hyphen - which would lead one to Customers which did not exist in September (the first image) there are still hyphens in October so in order for me to see ONLY new customers in October....now if I remove hyphen - from October filters it will give Customers which are NEW in only October and not presence in September (Second Image) The second image ID 5509629, the first ID if I see that specific ID in a list box in Qlik it shows me (Third Image) it has no record in September which proves the point there are new Customers. It shows one in straight table because it has a hyphen in Excel which leads Qlik to think there is a value there.

Sunny THANKS for your help so far. Please provide assistance in this matter.

Re: Difference between two months

sunny_talwar — Fri, 01 Apr 2016 22:27:47 GMT

I see the issue. The problem is that the row exists for when the amount is 0. Either you can exclude it anytime the value is 0 or you can use set analysis to exclude it. I am going to try both the ways now

Re: Difference between two months

sunny_talwar — Fri, 01 Apr 2016 22:29:32 GMT

Method 1:

Use this expression for count:

Count({<Data = {'>0'}>}[Customer Internal ID])

Re: Difference between two months

sunny_talwar — Fri, 01 Apr 2016 22:32:16 GMT

Method 2: Use this script:

Data:

CrossTable(Month,Data,3)

LOAD [Customer Internal ID],

Average,

[% of Total],

[2015 09],

[2015 10],

[2015 11],

[2015 12],

[2016 01],

[2016 02]

FROM

STK.xlsx

(ooxml, embedded labels, table is Sheet1);

New:

LOAD monthname(date#(Month,'YYYY MM')) as MonthYear,

[Customer Internal ID],

Data

Resident Data

where [Customer Internal ID]<>'#N/A' and not IsNull([Customer Internal ID]) and Data > 0;

DROP Table Data;

and then Count() alone should work for you

Re: Difference between two months

Sat, 02 Apr 2016 16:58:16 GMT

Hi Sunny,

You're awesome. Can you please tell me a formula I can add to the expressions which will give me the difference between the Customer Internal IDs and the difference of the sum amount for the respected months. Total I want to have columns, one next to the Count and one next to the sum entailing the differences.

Please help....

Re: Difference between two months

sunny_talwar — Sat, 02 Apr 2016 17:40:22 GMT

May be this?

1) Count([Customer Internal ID])

2) Count([Customer Internal ID]) - If(Count([Customer Internal ID]) > 0, Above(Count({<MonthYear>}[Customer Internal ID])))

3) Sum(Data)

4) Sum(Data) - If(Sum(Data) > 0, Above(Sum({<MonthYear>}Data)))

Re: Difference between two months

Sat, 02 Apr 2016 22:29:06 GMT

Thanks Sunny, one last inquiry bugging me is why is there a difference between sum in Excel and Qlikview even though both the figures and numbers the same?

Please provide assistance.

Re: Difference between two months

sunny_talwar — Sun, 03 Apr 2016 03:59:04 GMT

The issue is related to your second last row on the Excel

Since the row doesn't have a number, Kush seem to have programmed the script to remove it

If that row is needed, then remove [Customer Internal ID] <> '#N/A' from the code above and the totals will match up.

Now if you have got everything resolved, I would suggest closing this thread down by marking correct answer and helpful answers

Best,

Sunny

Re: Difference between two months

Mon, 04 Apr 2016 12:42:09 GMT

Thanks community for all your help!!!!!!

Re: Difference between two months

Mon, 04 Apr 2016 16:00:49 GMT

Hi Sunny,

I know I declared this thread answered but there's one concern I have. The formula you provided are fabulous but when I filter the data in Excel for new customers I get a different number than in Qlik. Is there anyway to match the number in both. For the sake of simplicity I have substituted the hyphens with blanks.

Please provide assistance.

Re: Difference between two months

sunny_talwar — Mon, 04 Apr 2016 16:03:11 GMT

Do you have a particular example I can look at?

Re: Difference between two months

Mon, 04 Apr 2016 16:08:04 GMT

This is in Excel.

This is in Qlik

Re: Difference between two months

sunny_talwar — Mon, 04 Apr 2016 16:16:22 GMT

There are 71 customers which were there in Sept, but not in Oct.

Re: Difference between two months

Mon, 04 Apr 2016 16:32:34 GMT

Hi Sunny,

You've brought up a great point (the loss of customers, which is part of the analysis later on) but right now my crucial concern is the new customers. I think Qlik is taking under consideration both of these elements concurrently. 109 + 71 = 180 and the net of these two is 109 which Qlik displays. Is there any formula I can utilize to show the new customers only, in this case 180 please.

Thanks,

Re: Difference between two months

sunny_talwar — Mon, 04 Apr 2016 17:09:46 GMT

I will take a look at this and revert back as soon as possible

Re: Difference between two months

Mon, 04 Apr 2016 18:34:37 GMT

Hi Sunny,

Do you have any insights with regards to the issue outlined above?