Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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))
You have shown Expected result, Some what 70% can help you. If you provide Sample then it could be better approach
Dear Anil
For sample I need to debug with few rows. Will it be ok?
Regards
Chintan
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 ???
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.
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?
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.
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
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
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))