Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am stuck in one set analysis, Scenario is:
I have 3 columns - 1) ReportingDate 2) AccountCode 3) Balance.
ReportingDate is having different dates and I am writing an expression in Pivot table
Sum (Balance) so I am getting the sum based on ReportingDate selection or AccountCode selection.
Now my problem is:
I am passing all the ReportingDate values into a variable vComparisonDate and that variable I am using in Calendar object.
Suppose I have 3 ReportingDates like 2016-03-31, 2016-04-25, 2016-05-31 then vComparisonDate variable will have minimum value 2016-03-31 and maximum value 2016-05-31.
Based on the above I want to write one more expression which will show me the sum of balances for the date selected by ComparisonDate Calendar(Which is nothing but having the dates from ReportingDate). (Scenario is that user will select a different date for vComparisonDate than ReportingDate)
I have tried multiple expressions but could not achieve the result(Sometimes I am getting the same sum for ComparisonDate also which I am getting for ReportingDate):
sum({$<vComparisonDate=E(ReportingDate)>} [Balance])
sum({$<vComparisonDate=E(GetFieldSelections(ReportingDate))>}Balance)
sum( {$<ReportingDate = E({$<$(vComparisonDate)={ReportingDate}>})>} Balance)
sum({$<ReportingDate=>} If(ReportingDate=Date(vComparisonDate), 0, [Balance]))
sum({$<ReportingDate=>} If(ReportingDate=Date(vComparisonDate), 0, [Balance]))
sum( {$<ReportingDate ={$<ReportingDate={$(vComparisonDate)}>})>} [Balance] )
Sum({<$(vComparisonDate)='2016-03-31'>[Balance])
If(ReportingDate=Date(vComparisonDate), 0,sum({<ReportingDate>}[Balance]))
//If(ReportingDate=Date(vComparisonDate), 0, Sum({<ReportingDate={$(vComparisonDate)}>}[Balance]))
Please help me ASAP
Regards
Pushkar
Goodafternoon Pushkar,
-I am not sure what you want to achieve. If I look at the expressions you mention, it seems that you want the Sum(Balance) if your ReportingDate is not your ComparisonDate?
-And what is the Value of your Variable Name?:
-Could you make a simple QV file and upload it? Or show some printscreens?