Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
devarasu07
Master II
Master II

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

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)

ArticleSoldQtySOH
CK1-80030315-11


Note:

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

like sample,   

ArticleSoldQtysohhint
CK1-6003006100need to exclude this article from report
CK1-60050722-20need to be shown in report
CK1-6019014510need to be shown in report
CK1-6039029000need to exclude this article from report
SL1-6096004422need to be shown in report
CK1-803902685-3need to exclude this article from report

DevaThanks

8 Replies
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
Master II
Master II
Author

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 articlesoh.JPG

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

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
Master II
Master II
Author

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

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
Master II
Master II
Author

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
Champion II
Champion II

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)