sum values with condition measure based on a dimension

[#1]

Product_Type	Sales	Expected result : Product Type without sales
Type1	899	0
Type2	345	0
Type3	0	1
Type4	567	0
Type5	235	0
Type6	456	0
Type7	0	1
Type8	46	0
Type9	0	1

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

[sushil353]

Hi Try this:

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

[#3]

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]

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]

Try this

you can use full accumulation option for below expression

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

[#6]

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

[#7]

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]

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

[#9]

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

[jonathandienst]

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