8 Replies Latest reply on Aug 31, 2018 7:27 AM by Okechukwu Ossai

suggest

Hi,

Can anyone help me in the below scenario.Requirement is in the picture below if any better way to achieve things.

I tried using the below formula in the 3rd column measure but it is giving me the entire count of stores including the 0 of 2nd column.

Formula used by me are :

3rd Calculated field=(IF [2nd Calculated field]>0 then sum([Store Name (copy)]) END)

[2nd Calculated field]=(if [1st Calculated field]>=1 then 1 else 0 END)

[1st Calculated field]=COUNTD(IF NOT ISNULL([YTD Product Quantity]) THEN DATETRUNC('month', [Created Date]) END)

[Store Name (copy)]={ fixed [Channel], [Segment]:COUNTD([Store Name])}

• 1. Re: suggest

Could you please attach your data as twbx format.

Shin

• 2. Re: suggest

Hi Niranjan,

This is a duplication of the question in the other thread. Like I previously said, it is difficult to provide a solution that will meet your requirements by just looking at the image. I can see that you are taking numerous steps to get to the answer. This can be done quite easily if you can provide a sample dummy dataset. What you can do is to anonymize your data. Change the name of channels, segment and store names to anything like, ABC, XYZ, etc. If you post this dummy data, I will be able to get you a simpler solution much quicker.

Hope this helps.

Ossai

1 of 1 people found this helpful
• 3. Re: suggest

Hi Ossai,

Please find attached the workbook.

• 4. Re: suggest

Hi Niranjan,

See attached solution. You are almost there. I only added an extra dimension to your [Store Name (copy)] LOD formula.

I also added one other formula (In YTD?) which might be useful in some of your views.

This will split your channels, products and stores into two (Yes or No). 'Yes' for stores and channels in YTD and 'No' for those outside YTD.

One more thing, grand total should be calculated using sum for each measure in the view, as shown below.

Hope this helps.

Ossai

1 of 1 people found this helpful
• 5. Re: suggest

I don't want to use the Sub-Total function. You can see the below snapshot where as soon as i drop the Store Name from the Rows shelf. The count gives me 1. It should be 15.

• 6. Re: suggest

HI Niranjan,

I've modified the calculation and this should do it hopefully. There are lots of formulas in the workbook and it is a challenge figuring out what they are all meant to be doing. One thing to bear in mind is that FIXED LODs don't respond to quick filters, except the field is included in the formula or the filter is added to context.

Hope this helps.

Ossai

• 7. Re: suggest

Ossai

I achieved it by including one more calculated field=

{ FIXED [Channel], [Segment], [Store Name]: ([YTD TXN Count_Active])}

[YTD TXN Count_Active]=(if [YTD TXN Count]>=1 then 1 else 0 END)