topic Re: Comparing accounts in different time periods in QlikView

Comparing accounts in different time periods

Anonymous — Mon, 26 Feb 2018 11:48:28 GMT

I have a dataset which shows me transactions and the accounts associated with them:

Account_ID	Transaction_Date	Revenue	Clean_date
123	05-01-2017	100.50	2017/01
234	24-01-2017	50.21	2017/01
123	21-01-2018	253.19	2018/01
456	29-01-2018	432.65	2018/01
234	07-01-2018	324.94	2018/01
666	09-02-2017	234.08	2017/02
666	28-02-2018	378.94	2018/02
654	23-02-2017	342.87	2017/02
785	17-01-2018	324	2018/01

I would like to be able to show how many of the accounts spent in the selected month compared to the same month in the previous year. For example, if I select 2018/01 I would like to see if the account had revenue in 2017/01 (account 123 would meet this criteria).

I originally solved this within SQL, however my data is down to transaction level now and I am stuck on how to write an expression to provide me with the information, I want to show this at an aggregate level (1 account in total) and also a breakdown of the accounts.

Re: Comparing accounts in different time periods

YoussefBelloum — Mon, 26 Feb 2018 12:45:06 GMT

Hi,

what do you mean by "show how many of the accounts spent" ?

if we take the Account ID 123 for example, what do you expect as an output ?

Re: Comparing accounts in different time periods

Anonymous — Mon, 26 Feb 2018 12:51:08 GMT

I mean, how many accounts spent in both the selected month and the same month in the previous year.

Account 123 would be counted as 1, as I have selected 2018/01 and they have revenue within 2018/01 AND 2017/01.

Re: Comparing accounts in different time periods

YoussefBelloum — Mon, 26 Feb 2018 14:48:54 GMT

if you combine this with some IF

sum({<Transaction_Date={">=$(=Monthstart(AddMonths(Transaction_Date,-12)))<=$(=MonthEnd(AddMonths(Transaction_Date,-12)))"}>} Revenue)

you should be able to see the expected output.

in the attached app, as soon as you select Account 123 and date = 21/01/2018, the table in the middle will show you the revenue of that account on the last year on the same month, if there is no data on the last year same month related with your selection, nothing will appear.

test it and let me know

Re: Comparing accounts in different time periods

Anonymous — Mon, 26 Feb 2018 15:17:51 GMT

Thanks for the example, I had a play with it but it doesn't do what I need it to do unfortunately.

In my app I would like to select the below (2018/01) as an example, if you see in the bottom table account 123 appears and so does 456 as they have a transaction within that month, however, I would like a table just showing account 123 as they have transactions in 2018/01 AND 2017/01.

Re: Comparing accounts in different time periods

YoussefBelloum — Mon, 26 Feb 2018 15:24:14 GMT

EDITED !

So try this (I changed a little bit the expression)

sum({<Clean_date={">=$(=Date(Monthstart(AddMonths(Date(Date#(Clean_date,'YYYY/MM'),'YYYY/MM'),-12)),'YYYY/MM'))<=$(=Date(MonthEnd(AddMonths(Date(Date#(Clean_date,'YYYY/MM'),'YYYY/MM'),-12)),'YYYY/MM'))"}>} Revenue)

Here we can remove the Monthstart() and Monthend functions because it is YYYYMM field, we just need to put = sign to have all the month included on our set analysis.

Re: Comparing accounts in different time periods

Anonymous — Mon, 26 Feb 2018 15:34:05 GMT

Ah I see, okay this works a lot better. How do I show the accounts without having to select the account number? The end user wants to see accounts with transactions in both years, if I select the date, it should automatically show all accounts that meet the criteria. Thank you.

Re: Comparing accounts in different time periods

YoussefBelloum — Mon, 26 Feb 2018 16:06:17 GMT

EDITED

Try this on the expression part:

Only({$<Account_ID = P({1<Clean_date={"$(=Date(AddMonths(Date(Date#(Clean_date,'YYYY/MM'),'YYYY/MM'),-12),'YYYY/MM'))"}>} Account_ID)>} Account_ID )

here is the new expression:

just remove the 1 after the P function

Only({$<Account_ID = P({<Clean_date={"$(=Date(AddMonths(Date(Date#(Clean_date,'YYYY/MM'),'YYYY/MM'),-12),'YYYY/MM'))"}>} Account_ID)>} Account_ID )

Re: Comparing accounts in different time periods

Anonymous — Mon, 26 Feb 2018 16:25:26 GMT

This works perfectly, is this called Set Analysis? I will read up. Many thanks, I understand a lot better now.

Re: Comparing accounts in different time periods

YoussefBelloum — Mon, 26 Feb 2018 16:39:14 GMT

Yes this is called set analysis, and here it is a nested set analysis using P() function. It is advanced level, so try to be comfortable with simple set analysis syntax before

https://help.qlik.com/en-US/sense-cloud/Subsystems/CloudHub/Content/ChartFunctions/SetAnalysis/set-modifiers-implicit-fi…

P() & E() and where do you use them?