Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Experts,
I need help creating the following application/script. I have attached a Qlik document and .xls for you.
Let me give you some background--
I currently run a revenue report from my ERP system that I convert into Excel. Once in Excel, I VLOOKUP that information against a separate file that sits inside my computer to find missing (blanks/zeros) information.
The data from the ERP system is the following:
The data, called 'Reason's, sitting on my computer is the following:
The normal operation of myself pivoting the information in Excel first looks like this:
^ Notice the blanks? I need the 'Reason's to populate here... so I do an IFERROR/VLOOKUP against the 'Reasons' in Excel for the below desired outcome:
All help very much appreciated!
Try this:
Revenue:
LOAD [GL Date],
[Actual Amount],
[Phase/Matter Codes]
FROM
[Test1.xlsx]
(ooxml, embedded labels, table is Revenue);
Reasons:
CrossTable(Date, Value)
LOAD [Phase/Matter Codes],
[42517],
[42518]
FROM
[Test1.xlsx]
(ooxml, embedded labels, table is Reasons);
Join (Revenue)
LOAD [Phase/Matter Codes],
Date(Num#(Date)) as [GL Date],
Value
Resident Reasons;
FinalTable:
LOAD [Phase/Matter Codes],
[GL Date],
If(Len(Trim([Actual Amount])) = 0, Value, [Actual Amount]) as [Actual Amount]
Resident Revenue;
DROP Table Reasons, Revenue;
Pivot Table
Dimension:
[Phase/Matter Codes]
[GL Date]
Expression:
=If(Sum(Aggr(NODISTINCT Sum(If(IsNum([Actual Amount]), [Actual Amount])), [Phase/Matter Codes])) <> 0, If(SecondaryDimensionality() = 0 or Dimensionality() = 0, Sum(If(IsNum([Actual Amount]), [Actual Amount])), [Actual Amount]))
Sunny-
In the 'Reasons' excel tab, I am trying to add names to the Phase/Matter Codes. I have attached the spreadsheet. I am having trouble with adding that information to your solution.
How does name impact the final solution?
It does not. I was trying to correlate the Phase/Matter codes to those specific name fields, but I am running into errors.
Your answer is correct--thank you very much!
May be check this:
Revenue:
LOAD [GL Date],
[Actual Amount],
[Phase/Matter Codes]
FROM
[Test1 (1)NAMES.xlsx]
(ooxml, embedded labels, table is Revenue);
Reasons:
CrossTable(Date, Value, 5)
LOAD EMPLOYEE,
BOSS,
MANAGER,
DIRECTOR,
[Phase/Matter Codes],
[42517],
[42518]
FROM
[Test1 (1)NAMES.xlsx]
(ooxml, embedded labels, table is Reasons);
Join (Revenue)
LOAD EMPLOYEE,
BOSS,
MANAGER,
DIRECTOR,
[Phase/Matter Codes],
Date(Num#(Date)) as [GL Date],
Value
Resident Reasons;
FinalTable:
LOAD EMPLOYEE,
BOSS,
MANAGER,
DIRECTOR,
[Phase/Matter Codes],
[GL Date],
If(Len(Trim([Actual Amount])) = 0, Value, [Actual Amount]) as [Actual Amount]
Resident Revenue;
DROP Table Reasons, Revenue;
Sunny--
I made a mistake--I did not include multiple dates of the given Phase/Matter codes in the first example.
In the first example I sent, I provided one instance of Phase/Matter Codes (ie; W002, W003, W004 etc...) with an Actual Amount. In my master data, I have multiple of the same Phase/Matter Codes (W002 listed 50+ times with different Actual Amounts).
The beginning screenshot had one Actual Amount per Phase/Matter Code. See W002 for an Actual Amount of -1201.00.
My master data contains multiple Phase/Matter Codes per date. In the below example I have attached, W002 Phase/Matter Code is listed in all of May. The problem occurring is the total sum in your current solution is listed as '-', when the total is 6727.1 for that day.
^ For example, W002 for 5/1/2016 should have a sum of 6727.1 for that day, but it is showing up as '-'. The grand total is correct with the 6727.1
I have tried fixing different equations, but i'm not having success.
As always, thank you again for your help!
Try this:
=If(Sum(Aggr(NODISTINCT Sum(If(IsNum([Actual Amount]), [Actual Amount])), [Phase/Matter Codes])) <> 0,
If(SecondaryDimensionality() = 0 or Dimensionality() = 0, Sum(If(IsNum([Actual Amount]), [Actual Amount])),
If(IsText([Actual Amount]), [Actual Amount], Sum([Actual Amount]))))
That is correct--thank you!!
When I add another Phase/Matter Codes in the Reasons tab, for example W003, the pivot table is showing them as '-' instead of the reasons listed per date. W003 does not have any Actual Amounts (yet)--so I would like to show the reasons..
Should be my last question!
May be this
=If(Sum(TOTAL Aggr(NODISTINCT Sum(If(IsNum([Actual Amount]), [Actual Amount])), [Phase/Matter Codes])) <> 0,
If(SecondaryDimensionality() = 0 or Dimensionality() = 0, Sum(If(IsNum([Actual Amount]), [Actual Amount])),
If(IsText([Actual Amount]), [Actual Amount], Sum([Actual Amount]))))