topic Re: P() & E() Set analysis in QlikView

P() & E() Set analysis

Wed, 17 Feb 2016 11:39:15 GMT

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?

Re: P() & E() Set analysis

rubenmarin — Wed, 17 Feb 2016 12:58:30 GMT

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.

Re: P() & E() Set analysis

rubenmarin — Wed, 17 Feb 2016 13:02:46 GMT

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.

Re: P() & E() Set analysis

swuehl — Wed, 17 Feb 2016 13:06:12 GMT

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)

Re: P() & E() Set analysis

hic — Wed, 17 Feb 2016 13:07:43 GMT

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

Re: P() & E() Set analysis

Wed, 17 Feb 2016 13:35:20 GMT

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.

Re: P() & E() Set analysis

Wed, 17 Feb 2016 13:45:15 GMT

This is not working properly.

Re: P() & E() Set analysis

Wed, 17 Feb 2016 13:49:25 GMT

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.

Re: P() & E() Set analysis

Wed, 17 Feb 2016 14:40:17 GMT

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.

Re: P() & E() Set analysis

Wed, 17 Feb 2016 15:25:35 GMT

Can any one have an idea?

Re: P() & E() Set analysis

swuehl — Wed, 17 Feb 2016 21:07:50 GMT

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.

Re: P() & E() Set analysis

hic — Wed, 17 Feb 2016 21:33:40 GMT

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

Re: P() & E() Set analysis

rubenmarin — Wed, 17 Feb 2016 23:42:53 GMT

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()