topic sum values with condition measure based on a dimension in App Development

sum values with condition measure based on a dimension

Fri, 27 Jan 2017 05:34:25 GMT

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

Re: sum values with condition measure based on a dimension

sushil353 — Fri, 27 Jan 2017 05:50:06 GMT

Hi Try this:

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

Re: sum values with condition measure based on a dimension

Fri, 27 Jan 2017 06:01:39 GMT

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

rahulpawarb — Fri, 27 Jan 2017 06:08:11 GMT

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

sravanthialuval — Fri, 27 Jan 2017 06:21:55 GMT

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

Fri, 27 Jan 2017 06:57:25 GMT

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

Fri, 27 Jan 2017 07:05:34 GMT

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

rahulpawarb — Fri, 27 Jan 2017 12:22:39 GMT

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

Re: sum values with condition measure based on a dimension

Mon, 30 Jan 2017 04:51:03 GMT

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

jonathandienst — Mon, 30 Jan 2017 05:20:17 GMT

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

Mon, 30 Jan 2017 13:01:57 GMT

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.