comparing 2 date in OBIEE bar line chart using presentation variable

Question

Hi gurus,

i would like to ask a guidance to solve my problem, i have 3 fact column (SR, Baseline, and Traffic) and 2 dimension column (application ID and time),

the goal is, i would like to compare Traffic & SR from a certain date with baseline value (baseline value is just traffic value) from certain date (shown below)

for the prompt i have baseline date using presentation variable called @{pDate_base} and actual date using variable @{pDate_compare} , the problem is the analyses won't work when 2 of the prompt filled, it can only work if one of the prompt filled so the other value would be aggregated. this is formula used in analyses:

Baseline: FILTER("F Recharge"."Traffic" USING("D Time"."Datex"=@{pDate_base}))

Traffic: FILTER("F Recharge"."Traffic" USING("D Time"."Datex"=@{pDate_compare}))

SR: FILTER("F Recharge Summary"."SR" USING("D Time"."Datex"=@{pDate_compare}))

any guidance for this case?

thanks gurus,

Galih

Thomas Dodds · Answer

Why isn't TRAFFIC, SR & BASELINE conformed on the same Date?

If you pick a single date: 1/4/2018 do you NOT want TRAFFIC, SR & BASELINE for that date by hour?

If not what is the relationship between Date and Baseline Date?  Is it a year ago, month ago, etc?  We need a little more detail ... this isn't a prompting issue strictly - it appears to me to be more a modeling (data relationships) issue.

Robert Angel · Answer

I am not understanding your statement; - "analyses won't work when 2 of the prompt filled, it can only work if one of the prompt filled so the other value would be aggregated" - can you explain further? - measures are just about always aggregated so this is not making a lot of sense to me

Can you be explicit on the exact scenario this is not as you want it (both prompts populated with dates - yes?) and also what measure then is not showing the correct value and exactly what you want it to be showing.

Do you need a further prompt to divorce the behaviour of SR and Traffic?

Reading between the lines and looking at your illustration it sounds like you do not need SR prompted?