Difference between two months

[#1]

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.

[sunny_talwar]

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

[#3]

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,

[sunny_talwar]

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

[#5]

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.

[sunny_talwar]

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

[sunny_talwar]

Method 1:

Use this expression for count:

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

[sunny_talwar]

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

[#9]

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....

[sunny_talwar]

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)))