Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 2 sheets, the first one shows everything but the second ones measures has to be filtered by a specific text field.
Can this filtration be done in an expression? something like:
= [measure] where [textField] = '01'
or in my context:
= "FactBudget[EtemadAmount]" where "DimAccount[ClassID]" = '01'
Or i have to filter in the script?
In the script i can't filter by the text field because it's in a different table than the measures table (Star Schema) which are joined in the diagram
The script is:
Main Tab:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
//SET QvdPath = "E:\QlikView\Final Dashboards\Source Data\QVD\Student\";
SET QvdPath = "D:\BudgetTables\";
OLEDB CONNECT32 TO [Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DWH_FIN_Budget;Data Source=hesssrv1];
//Totals:
DimAccount:
SQL
evaluate('dimaccount');
STORE DimAccount into $(QvdPath)DimAccount.qvd;
drop table DimAccount;
DimBudgetDate:
SQL
evaluate('DimBudgetDate');
STORE DimBudgetDate into $(QvdPath)DimBudgetDate.qvd;
drop table DimBudgetDate;
FactBudget:
SQL
evaluate('FactBudget');
STORE FactBudget into $(QvdPath)FactBudget.qvd;
drop table FactBudget;
Totals Tab:
//الحالة المالية اجمالي
//------------Account Dimension--------------
'Account Dimension':
LOAD "DimAccount[AccountKey]" as AccountKey,
"DimAccount[AccountAlternativeKey]",
"DimAccount[ClassID]" as 'رقم الباب',
"DimAccount[ClassText]" as 'مسمى الباب',
"DimAccount[ItemID]" as 'رقم البند',
"DimAccount[ItemText]" as 'مسمى البند',
"DimAccount[SerialID]" as 'المسلسل',
"DimAccount[SerialText]" as 'مسمى المسلسل',
"DimAccount[SiteID]" as 'رقم الجهة',
"DimAccount[SiteText]" as 'مسمى الجهة',
"DimAccount[CostCenterID]" as 'رقم مركز التكلفة',
"DimAccount[CostCenterText]" as 'مسمى مركز التكلفة',
"DimAccount[BeneficiarySiteID]",
"DimAccount[BeneficiarySiteText]",
"DimAccount[SuperVisorSiteID]",
"DimAccount[SuperVisorSiteText]"
FROM
[$(QvdPath)DimAccount.qvd]
(qvd);
//-------------Date Dimension Table----------
'Date Dimension':
LOAD "DimBudgetDate[DateKey]" as DateKey,
"DimBudgetDate[FiscalYear]" as 'السنة المالية'
FROM
[$(QvdPath)DimBudgetDate.qvd]
(qvd)
Where "DimBudgetDate[FiscalYear]" >= 2007 ;
//-------------Budget Fact Table-------------
FactBudgetTemp:
LOAD max("FactBudget[RequestKey]") as MaxRequest;
LOAD "FactBudget[RequestKey]"
FROM
[$(QvdPath)FactBudget.qvd]
(qvd);
let vMaxRequest = FieldValue('MaxRequest',1);
//let vMaxRequest = Peek('MaxRequest',0,'FactBudgetTemp');
Drop Table FactBudgetTemp;
'Budget Fact':
LOAD "FactBudget[DateKey]" as DateKey,
"FactBudget[RequestKey]" as RequestKey,
"FactBudget[AccountKey]" as AccountKey,
"FactBudget[EtemadAmount]" as 'الاعتماد',
"FactBudget[FromAmount]" as 'من',
'Budget Fact':
LOAD "FactBudget[DateKey]" as DateKey,
"FactBudget[RequestKey]" as RequestKey,
"FactBudget[AccountKey]" as AccountKey,
"FactBudget[EtemadAmount]" as 'الاعتماد',
"FactBudget[FromAmount]" as 'من',
"FactBudget[ToAmount]" as 'إلى',
"FactBudget[EtemadAfterAmount]" as 'الاعتماد بعد التعديل',
"FactBudget[DirectConsumeAmount]" as 'المنصرف المباشر',
"FactBudget[ErtebatAmount]" as 'الارتباط',
"FactBudget[ConsumedErtebatAmount]" as 'المنصرف من الارتباط',
"FactBudget[RemErtebatAmount]" as 'المتبقي من الارتباط',
"FactBudget[TaklofaAmount]" as 'التكلفة',
"FactBudget[ConsumedTaklofaAmount]" as 'المنصرف من التكلفة',
"FactBudget[RemTaklofaAmount]" as 'المتبقي من التكلفة',
"FactBudget[ConsumedAllClasses]" as 'المنصرف لكل الابواب'
FROM
[$(QvdPath)FactBudget.qvd]
(qvd)
WHERE "FactBudget[RequestKey]" = $(vMaxRequest);
Table Viewer:
Hi,
Try something like this in your expression.
sum({<[ClassID]={01}>}[EtemadAmount])
Regards
ASHFAQ
Hi,
Try something like this in your expression.
sum({<[ClassID]={01}>}[EtemadAmount])
Regards
ASHFAQ