Sales of all Products

[niranjana]

Hi ,

 I have straight table where my dimension is company and measure is Sum(Sales).

 I need the sum of sales of all products falling in that company whose previous year sales was zero. So, I used this expression : 

sum({<Product={"=sum(PrevTL)=0"}>}TL_Value) 

It is working but it shows the sales of only one product. I didnt select any product. I need current year sales of all products whose previous year sales was zero. 

Thanks in advance,

Niranjana

[micheledenardi]

Try something like this:

sum( {$<TL_Value= {“=Sum({1<Year= {2021}>} TL_Value) = 0”}>} TL_Value)

You can replace 2021 with a flag in your master calendar

[niranjana]

Thanks,

I replaced 2021 with my value but this expression is not working. Can you tell me the expression with "PrevTL" field because previous year sales is represented by this field .

[niranjana]

Hi, I wrote this expression :

sum(aggr(sum( {< Product={"=Sum(PrevTL)=0"},Year={"$(=Year(Max(Date)))"},Month={"<=$(=Num(Month(Max(Date))))"},flag={'Onco'}>} TL_Value),Product,[Ret/Hosp]))

But it gives TLValue only for one product. There are 3 such products. Any help is appreciated.

Thanks

[PrashantSangle]

are you sure that other 2 product satisfy this condition?

1: those product have sales in current year.

2: Those product don't have sales in previous year.

3: those product come under flag = 'Onco'

for debugging purpose.

Create straight table

add Product as dimension and use your measure. check that it is showing value or not?

Regards,

Prashant Sangle

[niranjana]

Thanks for your reply Prashant. When I pull a table it is showing correctly with "Product" and "Ret/Hosp" as dimensions.  But the requirement is dimension is "Company" and sales of all products falling under that company whose previous sales is zero.

Thats why I aggregated.

[PrashantSangle]

Can you share you working file? or sample file?

[niranjana]

 These sales are correct but there is one more product with sales 79130 which is not appearing.

Thanks

Niranjana

[PrashantSangle]

Hi @niranjana ,

sorry but without sample file or working file, I can't help you to debug the issue.

Regards,

Prashant Sangle

[niranjana]

Hi Prashanth, I cant post the working file 😞 . But I am posting what I should get in the app.

TLSales YTD'20 is Prev TL and TLsales ytd 21 is TLValue. There are 3 rows: 2 rows for one product and one row for another. The product name is in unique code on right side. The expression is working only for one product which has more than one row. If there are three products one each row, its not working.  Also, the one row which has value 79310 that value is not coming.

Thanks