Display in Pivot Products with No Sales for month

[louwriet]

Hi ,

I am getting stuck with a senario where i need to display and count products that had no sales for a month. I have a product master table and transaction table that link with a product code. See attached excel files as example and qv model.

Normally when i look at sales for the month it show all products that had sales but what i am interrested is to know wich products had no sales for a specific month.

Say i have 6 products and for a month and there were only sales for 4 of these products, then i need to know the count of products that did not had sales and also display the product name(i ou). Sales people can the focus on these products and find out why therre were no sales.

I have created a small sample model to explain it better. I also attached a spreadsheet that simulate what i would like to have in a pivot.

I am not sure if this needs to be done in the script or if one can hanlde this in the expressions. Or may with different joins in script or with calculated dimensions.

Help would really be appreciated.

Regards

Louw

[PrashantSangle]

Hi,

Try like ,

=count({<prodcode={"=isnull(prodcode)"}>}prodcode)

Regards

[louwriet]

Hi ,

Thanks for reply, i have entered your suggestion as expression in pivot but it is returning '0' and not diplaying the products with no sales.

Regards

Louw

[sundarakumar]

Hi Louwrie,

I tried out a script verson. Hope his helps.

Regards,

Sundar

[stigchel · Accepted solution]

I've played with this to see if it could be done in the frontend, it is not very pretty but it works... It uses a Valuelist of the productnames as calculated dimension.

[louwriet]

Hi Piet Hein,

Thank you for the example, i have implemented in my model and it is working fine.

Much appreciated

Regards

[louwriet]

Hi Sundar,

Interresting approach, i had a look at it and can use this in other senarios that i have

Thank you !