Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

sum values with condition measure based on a dimension

Product_TypeSalesExpected result : Product Type without sales
Type18990
Type23450
Type301
Type45670
Type52350
Type64560
Type701
Type8460
Type901

I have a table like above one (agg all the rows based on sales) I am trying to create a measure like Product Type without sales

Used the expression =Count({$<Sales={"0"}>} distinct [Product_Type])

But it is not working ... I am getting '1' in all rows for the measure "Product Type without sales"

Just FYI, the actual table will have rows at day level . I have just showed you the aggregated table. So here sales is sum(sales) so that if I pull the product_type dimension it do a group by

can anybody help me to solve this??

Thanks

1 Solution

Accepted Solutions
Not applicable

Re: sum values with condition measure based on a dimension

Hi Jithu,

I think that you should use something like sum(If(AGGR(Sum(Sales), Product_Type)>0,1,0)). If you use this on a KPI object you should have the right value at least at Product Type level.

Regards,

Guillermo.

10 Replies
sushil353
Honored Contributor II

Re: sum values with condition measure based on a dimension

Hi Try this:

count(DISTINCT {<sales = {"<=0"}>} producttype)

Not applicable

Re: sum values with condition measure based on a dimension

Not working ...  still showing all values as '1' . Just FYI, the actual table will have rows at day level . I have just showed you the aggregated table. So here sales is sum(sales) so that if I pull the product_type dimension it do a group by

rahulpawarb
Valued Contributor II

Re: sum values with condition measure based on a dimension

Hello Jithu,

Hope you are doing good!

You can add another expression with following Definition:

//Product Type without Sales

If(AGGR(Sum(Sales), Product_Type)>0,0,1)

Regards!

Rahul

sravanthialuval
Contributor

Re: sum values with condition measure based on a dimension

Try this

you can use full accumulation option for below expression

count({<Sales={'0'}>} DISTINCT Product_Type)

img.PNG

Not applicable

Re: sum values with condition measure based on a dimension

Thanks This works as a dimension , But I want to create measure to count how many product_type has 0 values....

can you help me create a measure....

Not applicable

Re: sum values with condition measure based on a dimension

This will not work ... Please check the check that you have provided. If we have sales =0 then the value should be 1 else 0. I want to create measure to count how many product_type has 0 values....

rahulpawarb
Valued Contributor II

Re: sum values with condition measure based on a dimension

Hello Jithu,

Below is draft version of solution against your requirement (you can further refine it):

I added a left join to the existing table to get the Sum of Sales at Product_Type level.

LEFT JOIN

LOAD Product_Type,

     Sum(Sales) AS NewSales

Resident MyData

GROUP BY Product_Type;

Post reloading data I have created a measure as below:

count({<NewSales={0}>} Product_Type)

Hope this will help.

Thank you!

Rahul

Not applicable

Re: sum values with condition measure based on a dimension

Hi Rahul,       As I told in the post that is aggregated table . Actual table consists date,product number also . So product number and type will be unique only for a day. One type can have many products. like that I have data for 5 months. out of that I want to how many products doesn't have sales by filtering a time period.

Thanks for the helping out.

Under Product Type  I have sub type and subsubtype too... I want to get this for all these three levels. Instead of indivial group by is there any other method group by all these Product Type,Product sub Type,Product sub sub Type at one shot

Thanks,

Jithendra

MVP
MVP

Re: sum values with condition measure based on a dimension

The relevance and accuracy of suggestions is proportional to the amount of information your provide. So more information about your requirements, data structure and some sample date would help. Best of all would be to upload a small sample QS qvf file with some representative sample data and details about the expected results (taking into account the extra fields that you have now added to the requirement).

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser