While using pick & match in expression, null values in the dimensions are getting suppressed automatically

[Manish]

Hi All

I am facing this problem where when I am using Pick and Match in the expression as below, null values in the dimension are getting suppressed automatically.

Expression: 

Pick(Match(Selected_Date,'DailyReportDate','WeeklyReportDate',

Sum({<DailyReportDate={'>=$(=vStartDate)<=$(=vEndDate)'},RepFOSOPS={'Y'}>}Sales),

Sum({<WeeklyReportDate={'>=$(=vStartDate)<=$(=vEndDate)'},RepFOSOPS={'Y'}>}Sales)

)

Result in the straight table as follows.

Business	Sales
A	100
B	120
C	80
Total	320

Expression without Pick and Match:

Sum({<DailyReportDate={'>=$(=vStartDate)<=$(=vEndDate)'},RepFOSOPS={'Y'}>}Sales)

Result in the straight table as follows.

Business	Sales
A	100
B	120
C	80
-	20
Total	320

[barnabyd]

G'day @Manish, I think the use of a variable Input Box would make this code simpler and also remove the problem with pick/match. Use it as a drop-down with fixed values, or as buttons. Then your code becomes something like this ...

Sum( {<$(vReportDate)={'>=$(=vStartDate)<=$(=vEndDate)'},RepFOSOPS={'Y'}>}Sales)

See if you can find a tutorial to teach you how to set it up. It's worth it as I find it produces a nice output.

Cheers, Barnaby.