12 Replies Latest reply on Aug 30, 2018 7:56 AM by Niranjan Banarjee

    Is it possible in Tableau

    Niranjan Banarjee

      HI,

      How can we achieve the desired output in Tableau.Need a calculated field.?

        • 1. Re: Is it possible in Tableau
          John Sobczak

          You requirements are pretty vague as for example not defining what count is (even though one can figure it out) as well as not defining what differentiates the rows.  Having sample mock data would be very helpful in this case.

          • 2. Re: Is it possible in Tableau
            Niranjan Banarjee

            Requirement: I need the COUNTA formula which we use in Excel at YTD level.

             

            I am getting the unique count if I use the below formula at month level absolutely correct. But when I am trying to remove the Date field from the Column shelf i am only getting results in '1' or '0'. Whereas I am expecting it to add all '1' at row levels and give the result:

             

            if sum([YTD Product Quantity])>0 then 1 else 0 END.

             

            Hope it clarifies better.

            • 3. Re: Is it possible in Tableau
              suresh.gooty

              at least you need to write 12 lines of code to achieve this. if date=jan and value >0 then 1 end

              in this case if value is there then it will display one else null because we are not writing anything on else part. now you can use that field and use analysis totals it will display the total as 4 at right side...... there are many methods like this as it is not a big challenge... simple logic required for achieving this.... good luck.

              • 4. Re: Is it possible in Tableau
                Okechukwu Ossai

                Hi Niranjan,

                 

                Assuming you have one date field called [Order Date] and those values are sale amounts, then you can use the formula below. Modify it to match your field names.

                 

                If your data is exactly as shown in the image, then use the formula below;

                COUNTD(IF NOT ISNULL([Sales]) THEN DATETRUNC('month', [Order Date]) END)

                However, if you have values in prior years then use this instead;

                COUNTD(IF YEAR([Order Date]) = 2018 THEN

                    IF DATEPART('month', [Order Date]) >= 1 AND DATEPART('month', [Order Date]) <= 5 THEN

                        IF NOT ISNULL([Sales]) THEN DATETRUNC('month', [Order Date])

                        END

                    END

                END)

                Both formulas should work.

                 

                Hope this helps.

                Ossai

                1 of 1 people found this helpful
                • 5. Re: Is it possible in Tableau
                  Niranjan Banarjee

                  Simply Brilliant!

                   

                  It did helped so far.

                   

                  Cheers!!!

                  • 6. Re: Is it possible in Tableau
                    Okechukwu Ossai

                    You're welcome Niranjan. Glad it helped.

                     

                    I just read your other comment that you need this calculated at YTD level. If yes, then you can try this also.

                     

                    COUNTD(IF YEAR([Order Date]) = YEAR(TODAY()) AND DATETRUNC('month', [Order Date]) <= DATETRUNC('month', TODAY()) THEN

                        IF NOT ISNULL([Sales]) THEN DATETRUNC('month', [Order Date])

                        END

                    END)

                    Hope this helps.

                    Ossai

                    • 7. Re: Is it possible in Tableau
                      Niranjan Banarjee

                      If you can please help me understand the formula will be of good help.@ Ossai

                      • 8. Re: Is it possible in Tableau
                        Paulo Dantas

                        Hi Niranjan,

                         

                        please choose the correct answer to close the thread.

                         

                        Best regards.

                        • 9. Re: Is it possible in Tableau
                          Okechukwu Ossai

                          Hi Niranjan,

                           

                          I will use the last version of the formula to explain. Same explanation goes for the previous ones.

                           

                          COUNTD(

                          IF YEAR([Order Date]) = YEAR(TODAY())

                          AND DATETRUNC('month', [Order Date]) <= DATETRUNC('month', TODAY()) THEN

                              IF NOT ISNULL([Sales]) THEN DATETRUNC('month', [Order Date])

                              END

                          END)

                           

                          From your question, these are the assumptions;

                          1. You want a count of items recorded YTD
                          2. This count of item should be a discount count per month
                          3. Count months only if they have an item, exclude the months if they are null

                           

                          I used nested IF statements because there are several criteria and each feeds into the other.

                           

                          So, first thing is to ensure you are counting within the current year

                          IF YEAR([Order Date]) = YEAR(TODAY())

                          Then also make sure you are only counting months up to the current month.

                          AND DATETRUNC('month', [Order Date]) <= DATETRUNC('month', TODAY()) THEN

                          Once this above YTD condition is done then check for the next criteria. Return the months only if item (or sales) is not null for that month.

                          IF NOT ISNULL([Sales]) THEN DATETRUNC('month', [Order Date])

                           

                          Then I wrapped everything in

                          COUNTD()

                          The formula initially returns the months which have items and then it outputs the distinct count of those months.

                          Some people may count items instead of months. However, I decided to count months because I don't know how your data is structured and you are less likely to make mistakes if you are counting months.

                          Also, notice that I counted months and not individual dates.

                           

                          Hope this helps. Could you please mark my answer as correct?

                          Ossai

                          2 of 2 people found this helpful
                          • 10. Re: Is it possible in Tableau
                            Niranjan Banarjee

                            what is wrong with this?

                             

                             

                            C=(IF [YTD TXN Count_Active]>0 then sum([Store Name (copy)]) END)

                            • 11. Re: Is it possible in Tableau
                              Okechukwu Ossai

                              sum([Store Name (copy)]) is summing up all values (rows) for a particular dimension. This becomes a problem when you have duplicated rows or very granular dataset. If you drag [Number of Records] to the view, you will discover that it is  probably16. So it is summing up everything. The best approach will be to return a dimension and then do a distinct count of that dimension.

                               

                              Is  [YTD TXN Count_Active] an aggregated field?   Is [Store Name (copy)] always a constant value?

                              If yes, then change the formula to;

                               

                              C =    IF [YTD TXN Count_Active]>0 then MIN([Store Name (copy)]) END

                               

                              It will be great if you can add a sample dummy workbook, if you require further help.

                              • 12. Re: Is it possible in Tableau
                                Niranjan Banarjee

                                Hi Ossai below is the details of formulas used.

                                 

                                C=(IF [YTD TXN Count_Active]>0 then sum([Store Name (copy)]) END)

                                 

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

                                 

                                [YTD TXN Count]=COUNTD(IF NOT ISNULL([YTD Product Quantity]) THEN DATETRUNC('month', [Created Date]) END)

                                 

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