10 Replies Latest reply: Jan 30, 2017 8:01 AM by Guillermo Rio

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

• Re: sum values with condition measure based on a dimension

Hi Try this:

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

• 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

• 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

• 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....

• 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

Sum(Sales) AS NewSales

Resident MyData

GROUP BY Product_Type;

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

Hope this will help.

Thank you!

Rahul

• 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

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

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

• 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....

• 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.