exclude Product if both measure =0? (but need show negative value)

[devarasu07]

Hi All,

Please refer to the attachment, issue looks simple but when i apply the logic to my model it dosn't work. do you have any suggestion how to resolve this issue? Thanks

if  SoldQty=0 and soh=0 then exclude the article from the report.

What i've tried is below, but this expression suppress negative value as well,

=if(aggr(sum({<Week={$(vSOHWeek)}>}soh),ArticleNo,Color)<>0 and

aggr(sum({<Week={$(vSOHWeek)}>}SoldQty),ArticleNo,Color)<>0

,ArticleNo)

Expected output: attached report should show 186 article  (inclusive of the below article)

Article	SoldQty	SOH
CK1-80030315	-1	1

Note:

if i do apply the same expression it works in simple model, i think my link table model has issue.

like sample,   

Article	SoldQty	soh	hint
CK1-60030061	0	0	need to exclude this article from report
CK1-60050722	-2	0	need to be shown in report
CK1-60190145	1	0	need to be shown in report
CK1-60390290	0	0	need to exclude this article from report
SL1-60960044	2	2	need to be shown in report
CK1-80390268	5	-3	need to exclude this article from report

DevaThanks

[sunny_talwar]

I am not 100% sure what this means, but soh_date is null for CK1-80030315 and you are using this in your variable vSOHWeek

[devarasu07]

Hi Sunny,

Thanks for your prompt reply, Please refer to the updated attachment

for that article have sold qty is -1 and soh is 1 and we need to show this article

[sunny_talwar]

I get that... but your calculated dimension is using a set analysis which is using soh_date which is null for the id you are showing. Do you know what it is null? I mean trying to understand that you need the set analysis to include null? I am not sure I understand the business requirement to tell you what might be needed, but right now the soh_date is missing which seems to be causing the issue for you

[swuehl]

I also don't know what you are trying to achieve.

Your dimension is

=if(aggr(sum({<Week={$(vSOHWeek)}>}soh),ArticleNo,Color)<>0 and

aggr(sum({<Week={$(vSOHWeek)}>}SoldQty),ArticleNo,Color)<>0

,ArticleNo)

right?

You are using different set expressions in your expressions:

sum({<Year={$(vCurrentYear)},Week={$(vCurrentWeek)},MaterialHuddleGrpDesc ={'CK Shoes','SL Shoes','Kids Footwear'},StockType={'Seasonal'}>}SoldQty)

sum({<Year={$(vCurrentYear)},Week={$(vNextWeek)},MaterialHuddleGrpDesc ={'CK Shoes','SL Shoes','Kids Footwear'},StockType={'Seasonal'}>}soh)

If you are using the set expressions from the dimension, you will get a zero, so it's clear why the article is removed.

I think you are just looking at different result sets.

[devarasu07]

Hi Sunny,

Due to nprinting purpose i have used that variable. Without variable also its not working.

=if(aggr(sum(soh),ArticleNo)<>0 and

aggr(sum(SoldQty),ArticleNo,Color)<>0

,ArticleNo)

My requirement:

If both measure (soldqty and soh =0 ) then Dont show that  article in the chart.

Thanks

Deva

[swuehl]

Currently both measure needs to be different from zero to show the article, so if you want to hide the article if both measures are zero, you need to change AND to OR:

=if(aggr(sum(soh),ArticleNo)<>0 OR

aggr(sum(SoldQty),ArticleNo,Color)<>0

,ArticleNo)

[devarasu07]

hi Stefan,

Thanks for your kind reply.

The dimension is just ArticleNo.

(Calculated dimension i just  tried applied the business rule)

Background:

I have weekly requirements and then model has Common dimension with multiple fact table (used link table method)

Let say week 16 report should use below data

Data: sales and purchase order

(17-apr-2017 to 23-apr-2014)

SOH: 24-apr-2017 (this is week17 data it has to be shown in week 16)- refer the model in previous attachment.

Thanks

Deva

[vinieme12]

devarasu07 Haven't checked the app but, try like below expressions; with ArticleNo as Dimension

SUM({<ArticleNo = {"=SUM(soh)<>0"}>}soh)

SUM({<ArticleNo = {"=SUM(SoldQTY)<>0"}>}SoldQTY)

combine them as below

sum({<ArticleNo={"=sum(soh)<>0"}>+<ArticleNo={"=sum(QTR)<>0"}>}soh)

sum({<ArticleNo={"=sum(soh)<>0"}>+<ArticleNo={"=sum(QTR)<>0"}>}SoldQTY)