Issue in Chart

[Anonymous]

Dear All

I have a pivot table where in if I select any particular value in Column - Entity then expression shows correct value(Gross Collectable measure) for that Entity.

However if I deselect the particular Entity then expression does not calculate the correct value for that entity (It ignores the variable vEnd).

(Screenshots attached- Correct and Incorrect).

Gross Collectable expression is as below:

=sum( {< Day =  {"<=$(=$(vEnd))"}, NEW_AGENT_NAME={'*'},NEW_ENTITY_CODE={'CRP*'}}>}      Value)

where vEnd is defined as vEnd= round(($(vNoOfDays+1) -  CP))     

Here CP is number like 18, 15 etc.

I tried things like aggr and also passing the dimension in expression but it did not work. Is there any mistake ?

Kindly suggest if any changes need to be made.

Thanks & Regards

Chintan

[Anil_Babu_Samineni]

You have shown Expected result, Some what 70% can help you. If you provide Sample then it could be better approach

[Anonymous]

Dear Anil

For sample I need to debug with few rows. Will it be ok?

Regards

Chintan

[Anil_Babu_Samineni]

First try with sample data creation or else share that data. In fact, Debug data is not sufficient as i assume. Create dummy data and then share with us

vNoOfDays MEans ???

[Anonymous]

It is number of days in a month calculated as below

=num(Monthend(max(TDATE))) -Monthstart(min(TDATE)) .

Debug data is not able to show the issue.  

[Anil_Babu_Samineni]

Truly need data for this tackle.

Assume, =num(Monthend(max(TDATE))) -Monthstart(min(TDATE))

Here, TDATE has 2014 to Till date

MonthStart(Min(TDATE)) is 04-Mar-2016

MonthEnd(Max(TDATE)) is 31-Mar-2017

vNoOfDays --> 366 Days

Your set expression is

=sum( {< Day =  {"<=$(=$(vEnd))"}, NEW_AGENT_NAME={'*'},NEW_ENTITY_CODE={'CRP*'}}>} Value)

Sum({<Day = {"<= $(vEnd)"}>} Value)

Sum({<Day = {"<= 366"}>} Value) // Here, the condition work as before 366 date of Sum value is getting

Is this you are refering?

[Anonymous]

It is like one month will be selected. 

So vEnd will be ,  31 - CP  which is suppose 31-10=  21.

So sum of value should be only from 1st to 21st of the month. 

Now in the report when I select one entity -  then Gross Collectable is calculated for 21 days as required.

But when I deselect it(meaning view all entities in report ) then , it is calculated for the entire month.The variable vEnd does not take effect.

[sunny_talwar]

I don't think set analysis will work here. I think you will have to use Aggr(), but the exact expression will have to be tested. Would you be able to share a sample?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[Anonymous]

Dear Sunny

Kindly find attached sample.

You can see for Entity Code - CRP024494  , Gross Collectable is 65027065  when it is selected.   (vEnd=17,  so 17 days amount comes)

when deselected is comes to 123......     (The variable vEnd does not take effect and it shows value of entire month.)

Thanks & Regards

Chintan

[sunny_talwar · Accepted solution]

Try this:

=Sum({<NEW_AGENT_NAME={'*'},BILL_TYPE={'SELL'},NEW_ENTITY_CODE={'CRP*'},BRANCH_NAME-={'THIRUVANANTHAPURAM S L THEATRE'}>} Aggr(If(Day <= round((num(Monthend(max(TDATE))) -Monthstart(min(TDATE)) +1 - CP)), Sum({<NEW_AGENT_NAME={'*'},BILL_TYPE={'SELL'},NEW_ENTITY_CODE={'CRP*'},BRANCH_NAME-={'THIRUVANANTHAPURAM S L THEATRE'}>} TURNOVER)), ENTITY_CODE, Day))