Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to turn what i have below into a more dynamic set analysis for the latest 5 years. basically what this does is to sum up all cost for each FY in a line chart. any suggestions?
if ([FY Occurred]='FY 2012-2013',rangesum(above(total sum({$<[FY Occurred]={'FY 2012-2013'}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]='FY 2013-2014',rangesum(above(total sum({$<[FY Occurred]={'FY 2013-2014'}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]='FY 2014-2015',rangesum(above(total sum({$<[FY Occurred]={'FY 2014-2015'}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]='FY 2015-2016',rangesum(above(total sum({$<[FY Occurred]={'FY 2015-2016'}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]='FY 2016-2017',rangesum(above(total sum({$<[FY Occurred]={'FY 2016-2017'}>}[Amount Paid]),0,rowno(total)))
)))))
i have got as close as below, but its not getting me the same as above:
IF ((Year(YearEnd($(vWarehouseLoadDate), 0, 7))- MID([Financial Year (Date Injury Occurred)],9,4))<5,
dual (rangesum(above(total sum({$<[Financial Year (Date Injury Occurred)]={$(='FY '&MID([Financial Year (Date Injury Occurred)],4,4)&'/'&MID([Financial Year (Date Injury Occurred)],9,4))}>}[Amount Paid]),0,rowno(total))) ,
MID([Financial Year (Date Injury Occurred)],9,4)))
note: vWarehouseLoadDate=2017
[Financial Year (Date Injury Occurred)] = FY 2012/2013 FY 2013/2014 ...etc.
Hello David,
Trust that you are doing good!
Please refer given sample expression:
if([FY Occurred]= 'FY ' & (Year(Today())-5) & '-' & (Year(Today())-4), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-5) & '-' & (Year(Today())-4))"}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]= 'FY ' & (Year(Today())-4) & '-' & (Year(Today())-3), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-4) & '-' & (Year(Today())-3))"}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]= 'FY ' & (Year(Today())-3) & '-' & (Year(Today())-2), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-3) & '-' & (Year(Today())-2))"}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]= 'FY ' & (Year(Today())-2) & '-' & (Year(Today())-1), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-2) & '-' & (Year(Today())-1))"}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]= 'FY ' & (Year(Today())-1) & '-' & (Year(Today())), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-1) & '-' & (Year(Today())))"}>}[Amount Paid]),0,rowno(total)))
)))))
Hope this will be helpful.
Regards!
Rahul
Hello David,
Trust that you are doing good!
Please refer given sample expression:
if([FY Occurred]= 'FY ' & (Year(Today())-5) & '-' & (Year(Today())-4), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-5) & '-' & (Year(Today())-4))"}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]= 'FY ' & (Year(Today())-4) & '-' & (Year(Today())-3), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-4) & '-' & (Year(Today())-3))"}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]= 'FY ' & (Year(Today())-3) & '-' & (Year(Today())-2), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-3) & '-' & (Year(Today())-2))"}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]= 'FY ' & (Year(Today())-2) & '-' & (Year(Today())-1), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-2) & '-' & (Year(Today())-1))"}>}[Amount Paid]),0,rowno(total))),
if([FY Occurred]= 'FY ' & (Year(Today())-1) & '-' & (Year(Today())), rangesum(above(total sum({$<[FY Occurred]={"= $(='FY ' & (Year(Today())-1) & '-' & (Year(Today())))"}>}[Amount Paid]),0,rowno(total)))
)))))
Hope this will be helpful.
Regards!
Rahul
thank you for your suggestion, Rahul.
this section here does not evaluate: {'= $(='FY ' & (Year(Today())-5) & '-' & (Year(Today())-4))'}>}
worked after i changed to below:
{ "$(='FY ' & (Year(Today())-5) & '-' & (Year(Today())-4))"}>}
Hello David,
Extremely Sorry for putting erroneous expression (corrected my earlier response accordingly).
Glad that post modification it worked for you.
Regards!
Rahul