Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - am looking at providing an indicator in the dashboard based on comparing a passed value and a calculated one.
My data looks like this:
Account file
Subscription file - joined using an account number on both tables
I have attached sample data in a single excel with multiple sheets.
what I wish to do is compare the Total_Active_Subs on the Account Table with an expression which I have created which calculates active subscriptions.
if the value mismatches then it will show a value 'Split Account' - if it matches then it will show 'Fully Managed'.
Alternatively is it possible to do or better to this within the load script to create a resident table? is it possible to do within a chart?
what is the best way to do it?
thanks
Martin
Any ideas from anyone? thanks martin
Managed to solve this myself by creating a resident table initially then using an if statement in a table:
ACCOUNT:
LOAD
ACCOUNT_ACCOUNT_NUMBER,
SUB_ACCOUNT_NUMBER,
ACCOUNT_ACCOUNT_NAME,
ACCOUNT_TOTAL_ACTIVE_SUBS
FROM [lib://DATA/SPLITACCOUNT.xlsx]
(ooxml, embedded labels, table is Account);
SUBSCRIPTION:
LOAD
SUB_ACCOUNT_NUMBER,
Sub_Number,
SUB_STATUS
FROM [lib://DATA/SPLITACCOUNT.xlsx]
(ooxml, embedded labels, table is Subscription);
[SUBSCRIPTION COUNT BY ACCOUNT]:
LOAD SUB_ACCOUNT_NUMBER,
count(Sub_Number) as SUBS_LOADED_WITH_ACCOUNT Resident SUBSCRIPTION
Group By SUB_ACCOUNT_NUMBER;
Followed by:
if (ACCOUNT_TOTAL_ACTIVE_SUBS = SUBS_LOADED_WITH_ACCOUNT,'No','Yes')