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?

[swuehl]

As mentioned before, the set analysis is evaluated once per chart, not per dimension line.

So you can't have a set expression consider your dimension values.

[When you select a single MONTHNAME, the dollar sign expansions should return a value, and the dimension should be limited to this value as well. Your sum should return the correct result (but this only works because your dimension is limited to a single value, same single possible value as you can retrieve in global context)

You can use a Sum(If(..)) construct instead, or try what I've suggested above (assigning the possible MONTHNAME values using p() function to DISCH_MONTH_NM), or look into the different approaches that Henric suggested.

[hic]

You can see that the =only(MONTHNAME) has failed to evaluate - it only shows {'-'} - which means that no value is assigned to DISCH_MONTH_NM. The reason is that it is expanded before the chart is built. Also I suspect that you will have have problems since the field is in a logical island.

I think that you should consider removing the logical island and instead use a canonical date.

HIC

[rubenmarin]

Hi, I made a quick sample emulating you model. Added a new month-year field to the charges table to use in the If() comparison, PFA.

Consider to use the canonical date proposed by Henric to avoid the If() inside the Sum()