Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
aejohnson
Contributor III
Contributor III

Comparing accounts in different time periods

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

Account_IDTransaction_DateRevenueClean_date
123

05-01-2017

100.502017/01
23424-01-201750.212017/01
12321-01-2018253.192018/01
45629-01-2018432.652018/01
23407-01-2018324.942018/01
66609-02-2017234.082017/02
66628-02-2018378.942018/02
65423-02-2017342.872017/02
78517-01-20183242018/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.

1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

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.

View solution in original post

9 Replies
YoussefBelloum
Champion
Champion

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 ?

aejohnson
Contributor III
Contributor III
Author

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.

YoussefBelloum
Champion
Champion

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

aejohnson
Contributor III
Contributor III
Author

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.

exa.PNG

YoussefBelloum
Champion
Champion

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.

aejohnson
Contributor III
Contributor III
Author

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.

YoussefBelloum
Champion
Champion

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 )

aejohnson
Contributor III
Contributor III
Author

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

YoussefBelloum
Champion
Champion

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

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