Author Topic: Need help in inputting 2 if conditions in one expression  (Read 778 times)

Offline harikk

  • Full Member
  • ***
  • Join Date: Aug 2021
  • Posts: 12
  • Forum Citizenship: +0/-0
Facing below error for an if condition I wrote. Please help.

XQE-PLN-0264 The argument at position 2 of the function 'if' is invalid in data item 'Data Item1' of query 'QueryMain', expected one of the following types: 'value'.
RSV-SRV-0042 Trace back:

Below are my data items. If I remove, [ReportingPeriod] = 'P13/14' , the statement works fine. I don't how to use two different conditions in and. Please help
[Data Item1] >

if (?pPeriod? = 'Reporting Period' and [ReportingPeriod] = 'P13/14' )
then ([P13Weeks]) else ([Reporting Period])

[ReportingPeriod] > descandants([Reporting Period],1)

Offline oscarca

  • Statesman
  • ******
  • Join Date: Jul 2018
  • Posts: 297
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #1 on: 04 Oct 2021 08:55:02 am »
Try rewriting it as a case-statement:

CASE
      WHEN ?pPeriod? = 'Reporting Period' and [ReportingPeriod] = 'P13/14'
             THEN [P13Weeks]
      ELSE [Reporting Period]
END

Offline harikk

  • Full Member
  • ***
  • Join Date: Aug 2021
  • Posts: 12
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #2 on: 04 Oct 2021 09:23:51 am »
I tried this but I am facing below error

XQE-PLN-0264 The argument at position 1 of the function '=' is invalid in data item 'Data Item1' of query 'QueryMain', expected one of the following types: 'value'.

Offline oscarca

  • Statesman
  • ******
  • Join Date: Jul 2018
  • Posts: 297
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #3 on: 04 Oct 2021 10:23:42 am »
My bad should be:

CASE
      WHEN ?pPeriod? = 'Reporting Period' and caption([ReportingPeriod]) = 'P13/14'
             THEN [P13Weeks]
      ELSE [Reporting Period]
END

Since you are working with dimensional data it expects a member not a string, so you have to caption [ReportingPeriod] to be able to match it with 'P13/14'
« Last Edit: 04 Oct 2021 10:27:47 am by oscarca »

Offline harikk

  • Full Member
  • ***
  • Join Date: Aug 2021
  • Posts: 12
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #4 on: 04 Oct 2021 10:39:34 am »
Apologies, It looks like an Invalid statement

XQE-PLN-0264 The argument at position 2 of the function 'WHEN' is invalid in data item 'Data Item1' of query 'QueryMain', expected one of the following types: 'value'.


Offline oscarca

  • Statesman
  • ******
  • Join Date: Jul 2018
  • Posts: 297
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #5 on: 04 Oct 2021 11:53:05 pm »
What is the parameter ?pPeriod? Passing? A string or a member ?

Offline harikk

  • Full Member
  • ***
  • Join Date: Aug 2021
  • Posts: 12
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #6 on: 05 Oct 2021 02:20:30 am »
It's a member. How to check the parameter data type

Offline oscarca

  • Statesman
  • ******
  • Join Date: Jul 2018
  • Posts: 297
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #7 on: 05 Oct 2021 03:05:41 am »
Write the expression like this then:
CASE
      WHEN ?pPeriod? = 'Reporting Period' and caption([ReportingPeriod]) = 'P13/14'
             THEN caption([P13Weeks])
      ELSE caption([Reporting Period])
END

Offline harikk

  • Full Member
  • ***
  • Join Date: Aug 2021
  • Posts: 12
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #8 on: 05 Oct 2021 06:00:48 am »
I am able to get past this error but I am not getting any values for my calc member which I have put this case statement in.

This is probable because of my wrong selection of member caption in the when statement. first child of [Reporting Period] is dynamic and changes as P04,P12,P13/14 etc..  May I know the easiest way to get the firstchild hierarcy please? Below is not fetching any data for me as I need to get 4 weeks for each period.

CASE
   WHEN (?pChoosePeriod? = 'Reporting Period' and roleValue ( '_memberCaption' , [Reporting Period]) = 'P04') THEN ([P04 (P-P04)])
   WHEN (?pChoosePeriod? = 'Reporting Period' and roleValue ( '_memberCaption' , [Reporting Period]) = 'P12') THEN caption([P12Weeks])
   WHEN (?pChoosePeriod? = 'Reporting Period' and roleValue ( '_memberCaption' , [Reporting Period]) = 'P13/14') THEN caption([P13Weeks])   
      ELSE caption([P13 (P-P13)])
END


Offline oscarca

  • Statesman
  • ******
  • Join Date: Jul 2018
  • Posts: 297
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #9 on: 05 Oct 2021 08:32:10 am »
To get the first child of the an hierarchy you can write firstChild(currentMember([Cube].[Dimension].[Hierarchy]
))

Offline harikk

  • Full Member
  • ***
  • Join Date: Aug 2021
  • Posts: 12
  • Forum Citizenship: +0/-0
Re: Need help in inputting 2 if conditions in one expression
« Reply #10 on: 05 Oct 2021 08:44:56 am »
I have a member hierarcy as below..

Cube - Profit & Loss
Dimension - Periods
Hierarcy - Periods
Member I want to get the first child from - [Reporting Period]

Method 1:

CASE
   WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods].[Reporting Period])) = 'P04' ) THEN ([P04 (P-P04)])
   WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods].[Reporting Period])) = 'P12') THEN caption([P12Weeks])
   WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods].[Reporting Period])) = 'P13') = 'P13/14') THEN caption([P13Weeks])   
      ELSE caption([P13 (P-P13)])
END

Error: error saying [Reporting Period] is wrong


Method 2:

CASE
   WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods])) = 'P04' ) THEN ([P04 (P-P04)])
   WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods])) = 'P12' ) THEN caption([P12Weeks])
   WHEN (firstchild(currentMember([Profit_and_Loss].[Periods].[Periods])) = 'P13' ) = 'P13/14') THEN caption([P13Weeks])   
      ELSE caption([P13 (P-P13)])
END

Error: XQE-EXE-0044 Data Types "numeric" and "string" cannot be compared