Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have following code,
=fabs(SUM(AGGR(if(( (vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=181)),SUM({$<ItemType_Name={'Raw Material'}>}
Inv_Val)),Location_Code, Item_Code, [Lot No_])))
Now the thing is i only want to display top 300 items on this list ,
I have decided to use rank on the above expression so that i would be able to pick top 300 items,
how would i frame the same...
please help..
Thanks,
Rohit
Use the dimension limits instead of rank.On the dimensions limits tab, choose Show only and type in 300 and you get the to 300 by size.
I have items whose inventory are present at different locations, and that is making my life tough now,
I want to sort the following table
and th expression for thr expression column is
=fabs(SUM(AGGR(if(( (vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=181)),SUM({$<ItemType_Name={'Raw Material'}>}
Inv_Val)),Location_Code, Item_Code, [Lot No_]))) and the table is
Item Name | Location | > 181 Days |
Total | 42,873,369 | |
Paracetamol ( Gal) B.P - Ziejan Kangle | MFG-GOA I | 3,284,960 |
Paracetamol ( Gal) B.P - Ziejan Kangle | MFG-GOA IV | 0 |
Paracetamol ( Gal) B.P - Ziejan Kangle | MFG-BADDI I | 46,510 |
Paracetamol ( Gal) B.P - Ziejan Kangle | TOOLS-R&D | 0 |
Hyprellose(Methocel K15 M Premium) Ph.Eur | MFG-GOA I | 35,135 |
Hyprellose(Methocel K15 M Premium) Ph.Eur | MFG-GOA IV | 2,506,455 |
Hyprellose(Methocel K15 M Premium) Ph.Eur | MFG-BADDI I | 0 |
(+)DIP CHLORIDE IN HEXANE | MFG-PATALGANGA | 1,868,987 |
Timolol Maleate U.S.P | MFG-GOA II | 1,666,298 |
Timolol Maleate U.S.P | TOOLS-R&D | 0 |
ALLOPURINOL BP | MFG-GOA IV | 1,567,681 |
Aripiprazole | MFG-GOA I | 1,197,519 |
Bromfenac Sodium sesquihydrate Inhouse | MFG-GOA II | 0 |
Bromfenac Sodium sesquihydrate Inhouse | MFG-GOA II PROJECTS | 1,195,342 |
DORZOLAMIDE HYDROCHLORIDE USP | MFG-GOA II | 0 |
DORZOLAMIDE HYDROCHLORIDE USP | MFG-GOA II PROJECTS | 1,148,348 |
RASAGILINE MESYLATE IN HOUSE | MFG-GOA IV PROJECTS | 1,117,050 |
H.P.M.C. E-5(Hypromellose) E.P | MFG-GOA I | 29,614 |
H.P.M.C. E-5(Hypromellose) E.P | MFG-GOA IV | 819,007 |
H.P.M.C. E-5(Hypromellose) E.P | MFG-BADDI I | 6,472 |
TROPINE | MFG-RABALE API | 795,286 |
Olanzapine Form-I USP | MFG-GOA I | 0 |
Olanzapine Form-I USP | MFG-GOA IV | 0 |
Olanzapine Form-I USP | MFG-GOA IV PROJECTS | 747,519 |
SIMVASTATIN Ph.Eur (BIOCON) | MFG-GOA I | 733,388 |
ADAPALENE USP | MFG-GOA I | 697,030 |
Peg-5 Glycerol Sterate- Arlatone -983s Inhouse | MFG-GOA I | 674,943 |
3(CARBAMOYL-METHYL)-5-METHYLHEXAMIC ACID | MFG-RABALE API | 618,005 |
3(CARBAMOYL-METHYL)-5-METHYLHEXAMIC ACID | LLM-INNOVATIVE | 0 |
Metformin Hydrochloride Ph.Eur | MFG-GOA I | 487,917 |
Metformin Hydrochloride Ph.Eur | MFG-GOA IV | 0 |
Metformin Hydrochloride Ph.Eur | MFG-BADDI I | 105,434 |
(6S)-6-METHYL-4-OX-5,6-DIHYDRO-4H-THIAO[(2,3-B) | MFG-RABALE API | 549,213 |
(6S)-6-METHYL-4-OX-5,6-DIHYDRO-4H-THIAO[(2,3-B) | MFG-PATALGANGA | 0 |
ALLOPURINOL PH.EUR | MFG-GOA I | 486,853 |
ALLOPURINOL PH.EUR | MFG-GOA IV | 0 |
ALLOPURINOL PH.EUR | MFG-GOA IV PROJECTS | 11,551 |
POVIDONE ( PVPK-25 ) I.P | MFG-WALUJ | 23,042 |
POVIDONE ( PVPK-25 ) I.P | MFG-BADDI I | 0 |
POVIDONE ( PVPK-25 ) I.P | MFG-BADDI II | 468,172 |
SPENT PALLADIUM ON CARBON | MFG-PATALGANGA | 0 |
SPENT PALLADIUM ON CARBON | MFG-KILO LAB | 470,526 |
SPENT PALLADIUM ON CARBON | LLM-HINDUSTAN | 0 |
Homatropin Hydrobromide I.P. | MFG-GOA II | 458,332 |
Homatropin Hydrobromide I.P. | TOOLS-R&D | 0 |
Homatropin Hydrobromide I.P. | NORRIS MEDICINES LTD (VIRTUAL) | 0 |
P.V.P.K-30 (Shweta Pharma/Novo) B.P | MFG-GOA I | 444,289 |
P.V.P.K-30 (Shweta Pharma/Novo) B.P | MFG-BADDI I | 0 |
TRANS - 4 - METHYL CYCLOHEXYL AMINE | MFG-RABALE API | 409,890 |
Sepifilm (752 White) For Citalopram - Kent InHouse | MFG-GOA I | 388,547 |
Fluoxetine Hydrochloride Ph.Eur | MFG-GOA I | 367,622 |
Propylene Glycol IP/BP | MFG-GOA I | 775 |
Propylene Glycol IP/BP | MFG-GOA IV | 0 |
Propylene Glycol IP/BP | MFG-GOA II | 4,824 |
Propylene Glycol IP/BP | MFG-WALUJ | 2,528 |
Propylene Glycol IP/BP | MFG-BADDI I | 1,775 |
Propylene Glycol IP/BP | MFG-BADDI II | 48,855 |
Propylene Glycol IP/BP | LLV-LESANTO | 238,866 |
Propylene Glycol IP/BP | LLV-SAKAR | 41,961 |
Propylene Glycol IP/BP | LLV-RAHUL | 248 |
Propylene Glycol IP/BP | LLV-DCI | 0 |
Propylene Glycol IP/BP | IN-TRANSIT | 0 |
You can choose which dimension you want the limit on in the dimension limits settings. -John
Hey John,I know that option, but it has a limit upto 100,
I want to show top 300 values..
=aggr(if(Rank(fabs(SUM(AGGR(if(( (vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=181)),SUM({$<ItemType_Name={'Raw Material'}>}
Inv_Val)),Location_Code, Item_Code, [Lot No_]))))<300,Item_Name),Item_Name,Location_Name,Location_Name_Short)
Above code i am using But aint working..
Are you using that formula as calculated dimension, or as expression? It is not clear.
From the look at that formula it seems to me you are trying to return several fields (dimensions) from external aggr() as a set of calculated dimensions. If so - I believe that is impossible.
But you can achieve same result another way:
Create calculated dimension by a rank function. Formula for that calculated dimension would be something like
Rank(if((vSelectDate - max({$<StockInOutIndicator={1}>}PostingDate) >=181),SUM({$<ItemType_Name={'Raw Material'}>} Inv_Val)),Location_Code, Item_Code, [Lot No_]). That dimension will return numeric rank for each row
Make that dimension invisible
Set dimension limit for that dimension to 300 minimal values
Ordinary dimensions (Location_name, Item_Name) remains unchanged