Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Below is my dataset, where I need to calculate average of Amt basis below 2 criterias
1. Status = Won, Sales Members = Alex and Martha, SalesTeam1 = BDE1 and BDE2
2. Status = Won, Sales Members <> Alex and Martha, SalesTeam1 <> BDE1 and BDE2, Host = BCD
Second condition should exclude all the ID's that are already covered in first condition e.g. ID's 567779 and 573899 (these are not part of first calculation)
Would really appreciate if anyone can tell me the solution for this problem
ID | Amt | Status | Host | Close Year | SalesTeam1 | Sales Members |
352579 | €19,896 | Lost | ABC | 2021 | BDE1 | Alex |
352579 | €13,260 | Won | ABC | 2022 | BDE2 | Martha |
494308 | €532,440 | Won | BCD | 2023 | BDE1 | John |
494308 | €18,960 | Won | BCD | 2022 | Team4 | Catherine |
494308 | €7,188 | Won | BCD | 2023 | BDE1 | Jonas |
494308 | €3,936 | Won | BCD | 2022 | BDE2 | Alex |
543228 | €5,640 | Won | ABC | 2023 | Team3 | Martha |
553000 | €6,181 | Won | ABC | 2022 | Team4 | John |
554930 | €10,404 | Won | ABC | 2023 | BDE1 | Catherine |
561126 | €43,440 | Stopped | BCD | 2022 | BDE2 | Jonas |
562014 | €11,016 | Stopped | BCD | 2023 | Team3 | Alex |
564001 | €3,936 | Stopped | BCD | 2023 | Team4 | Martha |
566524 | €206,453 | Stopped | BCD | 2023 | BDE1 | John |
567452 | €5,976 | Won | BCD | 2023 | BDE2 | Catherine |
567779 | €28,783 | Won | BCD | 2023 | Team3 | Jonas |
567779 | €120,000 | Won | BCD | 2023 | Team4 | Alex |
571459 | €15,156 | Won | BCD | 2023 | BDE1 | Martha |
572776 | €21,480 | Won | BCD | 2023 | BDE2 | John |
573899 | €116,760 | Won | BCD | 2023 | Team3 | Catherine |
574905 | €21,060 | Won | EFG | 2023 | Team4 | Jonas |
574905 | €16,380 | Lost | EFG | 2023 | BDE1 | Alex |
574905 | €10,908 | Lost | EFG | 2023 | BDE2 | Martha |
574905 | €5,064 | Lost | EFG | 2023 | BDE1 | John |
575138 | €6,300 | Lost | EFG | 2023 | Team4 | Catherine |
576611 | €11,928 | Won | EFG | 2023 | BDE1 | Jonas |
576611 | €377,911 | Won | EFG | 2023 | BDE2 | Alex |
576611 | €2,645 | Won | EFG | 2023 | Team3 | Martha |
576611 | €5,400 | Won | EFG | 2023 | Team4 | John |
576611 | €12,264 | Won | EFG | 2023 | BDE1 | Catherine |
=Avg({$<[BDE Flag]={1},Status={'WON'},Role={'BDE1','BDE2'},Year={2023}>} Amt)
Perhaps, it's the currency sign. Try this the in script:
LOAD ID,
Amt,
Num(PurgeChar(Amt,'€')) as Amt,
...
FROM SourceTable;
Hi Mayil,
There is no output when I use this expression, just showing '-'
Hi,
The problem that I have is that I don't have BDE Flag in Qlik, I created it in excel but in Qlik I am getting data from multiple tables and there is no single table where I can add this flag.
Do you think is there any other way to get this output or the only option left is to create this flag in Qlik.
Please advise.
Here's how to create the flag in the script.
[BDE NAMES]:
LOAD * Inline [
BDE TEAM
CLAIRE STRAITON
CALLUM FERGUSON
ARIAN MATZ
JEROEN VAN DEN BERG
EVELYN LIM ];
Data:
LOAD *,
If([BDE TEAM] = 1 and Match(Role,'BDE1','BDE2'),1,0) as [BDE Flag];
LOAD ID,
Status,
VP,
Date,
Year,
Amt,
Role,
Team_Name,
If(Exists([BDE TEAM],Team_Name),1,0) as [BDE TEAM]
FROM [...\Data_2023.xlsx] (ooxml, embedded labels, table is Sheet1);
DROP Table [BDE NAMES];
Hi
I have added the Flag now and used the below formula:
=Avg({$<[BDE Flag]={1},Status={'WON'},Role={'BDE1','BDE2'},Year={2023}>} Amt)
But my output is 12,599, I am using this formula in KPI, is there a problem with this
This is my output. You might now need to share the application.
Thanks a lot buddy, it works for me now.
Could you please help me second set of expression and let me know your result
=Avg({$<[BDE Flag]={0},Status={'WON'},VP={'DZ'},Year={2023}>} Amt)
Thanks a lot for all the help and support in solving my problem