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

[sarasmonu]

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

[ogautier62]

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

[sarasmonu]

Hi Gautier,

Thanks for your reply.

I am not very clear with the explaination.

Can you please give an example

Thanks

[ogautier62]

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