topic Re: Issue in Chart in QlikView

Issue in Chart

Anonymous — Thu, 02 Mar 2017 08:15:13 GMT

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

Re: Issue in Chart

Anil_Babu_Samineni — Thu, 02 Mar 2017 08:18:32 GMT

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

Re: Issue in Chart

Anonymous — Thu, 02 Mar 2017 09:35:52 GMT

Dear Anil

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

Regards

Chintan

Re: Issue in Chart

Anil_Babu_Samineni — Thu, 02 Mar 2017 10:35:17 GMT

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

Re: Issue in Chart

Anonymous — Thu, 02 Mar 2017 10:39:57 GMT

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.

Re: Issue in Chart

Anil_Babu_Samineni — Thu, 02 Mar 2017 11:24:07 GMT

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?

Re: Issue in Chart

Anonymous — Thu, 02 Mar 2017 11:39:22 GMT

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.

Re: Issue in Chart

sunny_talwar — Thu, 02 Mar 2017 11:58:23 GMT

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

Re: Issue in Chart

Anonymous — Thu, 02 Mar 2017 12:47:43 GMT

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

Re: Issue in Chart

sunny_talwar — Thu, 02 Mar 2017 13:01:53 GMT

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

Re: Issue in Chart

Anonymous — Thu, 02 Mar 2017 16:25:54 GMT

I will try and let you know.

Re: Issue in Chart

Anonymous — Fri, 03 Mar 2017 07:28:32 GMT

Hello Sunny

Thanks for your help

It works correctly as required.

Why is the if condition used as always it needs to be calculated till the value for noofdays - CP.

Regards

Chintan

Re: Issue in Chart

sunny_talwar — Sun, 05 Mar 2017 12:27:58 GMT

Why is the if condition used as always it needs to be calculated till the value for noofdays - CP.

Is this a question for me? I am not sure I understand your question

Re: Issue in Chart

Anonymous — Tue, 07 Mar 2017 12:22:51 GMT

Just wanted to know why 'if' condition was used in expression.

Re: Issue in Chart

sunny_talwar — Tue, 07 Mar 2017 12:49:01 GMT

Because Set analysis is evaluated once per chart whereas your requirement needed you to evaluate the vEnd differently for each dimension. To do this, Aggr was the option unless you wish to make changes to your script and then you can use search string set analysis

Re: Issue in Chart

Anonymous — Wed, 08 Mar 2017 05:23:03 GMT

Thanks!