Query Exclusion

[Former Member]

I have two dates in my Purchase Order Cube; 1) Document date and 2) Invoice date.

Is it possible to write a query that only shows data where Document Date is later than the Invoice Date? I can see in the Query Designer that there are some exclusion options for key figures but nothing for dealing with dates.

Thanks!

[Former Member · Accepted solution]

Hi Patrick,

You need to create 2 formula variables of replacement path on your 2 dates. Then in the structure create a formula with these 2 variables difference. Say, it'll be greater zero or less than zero. Create a condition on this last formula and you are done!

Best regards,

Eugene

[former_member188325 · Accepted solution]

Hi Eugene,

Thats perfect!gr8!

[Former Member · Accepted solution]

That's what I tried to do but where do you create the 2 formula variables? I tried to in the query but couldn't ... do you do it in the cube?

[Former Member · Accepted solution]

No,

Let's say you create a formula in the structure. In the next screen, in the left frame, you'll see your KFs and variables. Right click on variables top node and create a new formula variable.

Best regards,

Eugene

[Former Member]

Hi,

Try doing with a condition. You can even try with an IF condition in the new formula.

Message was edited by: AVR - IGA

[Former Member]

That seems to be only for Key Figures. I do not have any keyfigures in my query so the option is dimmed out.

[Former Member]

Hi Patrick,

you can create Characteristic variable over the 2 dates and compare them.

Than have a condition to exclude the unwanted records..

Hope it helps

ashish..

[Former Member]

Hi,

Then you can do that by creating two variable and in the selection while restricting you can select ranges and in the right side you can set a condition.

[Former Member]

If I understand you correctly this will create a prompt and the user will have to enter a date range. I don't want the user to have to select a data range, I just want the exclusions to appear. They only want to see a list of all records where one date is greater than the other. I know I could probably create a new infoObject and in the update rules populate the new infoObject with a number that is the difference in days between the two dates but that seems like too much work. Seems like I should be able to do this in the query.

[Former Member]

I am thinking of a virtual characteristic.

[Former Member]

Hi John,

Do you know where I can find out more info on these 'virtual characteristics'? Thanks!