topic Use selected Filter value in the set expression to feed into another dimesion in App Development

Use selected Filter value in the set expression to feed into another dimesion

sarasmonu — Wed, 11 Jul 2018 09:05:45 GMT

Hi All,

I want the selected value from a filter dimension to be assigned to another dimension in the set expression.

Below is the scrnarion:

I have a 2 date field Complete Date and Submit Date. I need a report wherein in one sheet i have 2 bar graphs, one is based on Submit date and another on Complete date.

I need a common date filter wherein say i select 1/5/2018 and i need the metric where Submit date is 1/5/2018 for one bar chart and Complete Date is 1/5/2018 for another bar chart. Currently i am using Submit date in Filter and while i am creating metric for Complete Date bar chart i am trying to feed the value of Submit date into Complete date like the below:

Count({<[Comple Date] = p([Submit Date])>}distinct ordernumber). However i am not getting the desired result. Please help

Thanks,

Monu

Re: Use selected Filter value in the set expression to feed into another dimesion

ogautier62 — Wed, 11 Jul 2018 09:11:55 GMT

Hi,

in your case you need to make two joins with calendar,

one based on complete date, other on submit date

so,

load a new table with date of your calendar and either complete or submit date with a type : 'complete' or 'submit'

and then in the chart set analysis with type = {complete or submit}

regards

Re: Use selected Filter value in the set expression to feed into another dimesion

sarasmonu — Wed, 11 Jul 2018 09:58:20 GMT

Hi Gautier,

Thanks for your reply.

I am not very clear with the explaination.

Can you please give an example

Thanks

Re: Use selected Filter value in the set expression to feed into another dimesion

ogautier62 — Wed, 11 Jul 2018 11:31:03 GMT

suppose you have rows like this in script :

id submitdate completedate

1 01/01/18 12/01/18

make a new table like this

submitdate completedate typedate date (link with calendar)

01/01/18 submit 01/01/18

12/01/18 complete 12/01/18

with

load submitdate, completedate,if(iterno()=1,submitdate,completedate) as date, if(iterno()=1,'submit','complete') as typedate resident 'your table fact' while iterno()<=2