Total value appears correctly in table but individual values are wrong

[Iwin]

Hi All,

I want to display actual cost monthly .

Selections which i have done is 2021 Year.

However in indiviual months the values are getting summed up for the entire year and that value is appearing in each month.

The expression used is as follows

SUM({<CLASS_CODE1={'BILL_EQUAL_TO_REV'}, CLASS_CATEGORY={'BILL_TO_REV'},PROJECT_START_MONTH={"<=$(=monthend(max(CALDATE)))>=$(=monthstart(min(CALDATE)))"} >}[ACTUAL COST])

This is the set expression evaluated.

Here in below snapshot,the left table is showing the correct values when Project Start Month is used as dimension.

I want to show the same values but with using different dimension ie.CALDATE which is the requirement.

But the values are getting summed up,though the total is coming correctly.

What is the reason for this and how to show the correct indiviual values?

[Vegar]

You have cropped the image so we don't see the dimension used in the left object.

My best guess from what I see is that you are using an different dimension than in the left object and that dimension have a many to many relationship with your cost transaction data.

What does your data model look like? Could you add an image?

[Iwin]

Hi @Vegar 

The dimension used in the left object is Project_Start_Month which is directly linked with Actual cost.

CALDATE is directly linked with Revenue which shows correct values when CALDATE is used as dimension.

I want to show both the values in a single table.

Here is my model with canonical calendar which i tried but didnt work.

[Vegar]

Though one.

You could reattempt to  get the canocial date calendar to work.

Have you considered changing the datamodel. It could be easier to do what you are trying to do with a  single transaction table with cost and revenue in the same table. 

Take a look at this blog post (if you not already have seen it) : Canonical Date - Qlik Community - 1463578

[Iwin]

I refered the same article for canonical calendar.

Is there any way to show 2 expressions display values using 2 different dimensions in same table?like for eg using aggr or any set expression?

like i want sum(actual cost) wrt project start month and sum(revenue) wrt caldate

It is difficult to change data model to be honest.How should i change the data model as per your view?

[marcus_sommer]

From your description I assume that your issue isn't directly/only related to the datamodel else to the fact that you use $-sign expansions, like: ... $(=monthend(max(CALDATE))) ... within your expressions. This creates an adhoc-variable which is calculated globally without any dimensionality to your object before the chart is evaluated and the result is then used for each row.

In most scenarios you will need to transfer such checks into an if-loop to get a row-level evaluation or like already hinted some more or less expensive changes/adjustments within the datamodel ...

- Marcus  

[Iwin]

Hi @marcus_sommer ,

The reason for the expression 

PROJECT_START_MONTH={"<=$(=monthend(max(CALDATE)))>=$(=monthstart(min(CALDATE)))"}

is that Project Start Month is not linked with Master Calendar but CALDATE is and 2021 is the year selected in CALDATE field.

Attaching the data model 

Here BILL AMOUNT NEW is the revenue which works on CALDATE while ACTUAL COST works on PROJECT START MONTH.

What changes if any do u suggest in this case?

[marcus_sommer]

To be honest I suggest a complete re-design of the datamodel in the direction of a star-scheme - means merging the fact-tables per concatenate together. Within the simplest case they will be directly as they are concatenated and by a more complex dataset you may need in beforehand multiple join/mapping measures to add/clean/prepare there fields/field-values from other fact-parts and/or the dimensions.

Further you may need to create several master-calendars - maybe for each date-field an own calendar - and you may also need to implement a canonical calendar to be able to link the multiple date-fields.

This may sound like a lot of rather sophisticated work - and usually it is but often it's easier and less work as to handle a multi link-table approach like your screenshot hinted.

Only for your described issue you may try to outsource this check within an out-side if-loop but I assume that there are various further (performance) challenges within the required views so that it may be sensible to start with your datamodel at the beginning again.

- Marcus