Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi Try this:
count(DISTINCT {<sales = {"<=0"}>} producttype)
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
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
Try this
you can use full accumulation option for below expression
count({<Sales={'0'}>} DISTINCT Product_Type)
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....
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....
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
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
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).