P() & E() Set analysis

[#1]

The below is my Data Model.Vistis are joined to Charges table by Account ID.

Charges Table is joined to Calendar on POST DATE.

I have created an Independent Calendar to do the analysis.

Here is the problem,

When user selects a month from independent calendar, I want to fetch the same month from DISCH_MONTH_NM(Visit Table) and then fetch the possible Account IDs & then Calculating the Quantity for all those Accounts.

In the UI Level , I created a Straight table like this.

1st Expression : Only(MONTHNAME)  -- to Check weather it is fetching one value

2nd Expression : COUNT(DISTINCT if(MONTHNAME=DISCH_MONTH_NM, [Account ID]))  -- To check the number of Account IDs

3rd Expression : sum({<[Account ID]=P({<DISCH_MONTH_NM ={'$(=only(MONTHNAME))'}>}[Account ID])>} QUANTITY)  -- To fetch Quantity

But the 3rd Expression is not working...

Can any one correct this expression?

[rubenmarin]

Hi Jhansi, $-expanded expressions and set analysis are calculated for the whole table, not row by row. QV tries to expand the $-expression and it found different values for MONTHNAME so it returns null.

If you hover the mouse over the column title you'll see something like:

sum({<[Account ID]=P({<DISCH_MONTH_NM ={''}>}[Account ID])>} QUANTITY)

The 'If' version is calculated row by row.

[rubenmarin]

Btw, not tested (maybe later I have time to test and improve performance) but it can be something like:

Sum(if(MONTHNAME=DISCH_MONTH_NM, QUANTITY))

A sample would be nice to return a tested answer.

[swuehl]

You probably just need to copy the possible values from your data island to the Visits  table month field:

=Sum({<DISCH_MONTH_NM = p(MONTHNAME) >} QUANTITY)

[hic]

There could be a problem with the formatting of the month... If so, you may consider formatting both months as integers and use these in the set analysis. Do the DISCH_MONTH_NM and MONTHNAME have the same format?

But first: What does your label show? Here you can see if the dollar-expansion has worked the way you want.

Further, why do you keep the calendar as a logical island. I think a canonical date would be better. See Canonical Date.

HIC

[#6]

Yes. What you have explained is correct. It could not fetch the Month Values from MONTHNAME (Master table).

So What should be the right expression to reflect the value?

Thank you for responding.

[#7]

This is not working properly.

[#8]

This is working in a Text Object and giving the right result as shown in screen shot.

But giving the total amount for all the values.

[#9]

Thank you Henric for responding. As you stated. the expression that i have written is giving output as below screenshot.

Instead of using $(=only(DISCH_MONTH_NM)) , I should use some expression to pull the correct month. Could you make a suggest? Thanks a lot in advance.

[#10]

Can any one have an idea?