Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Issue in Chart

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

15 Replies
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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 ???

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anonymous
Not applicable
Author

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
Not applicable
Author

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

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