11 Replies Latest reply on Dec 11, 2019 3:39 PM by Abby Nie

Add another calculated row in the text table

Hi all,

I am trying to compare the sales for three fruits. The data is like this

What I have for now is:

My desired output is a table with the difference of sales between 2018 and 2019, such as the following:

The difference is the [sales in 2019] - [sales in 2018].

Can some one help me to achieve this?

Thanks!

Abby

• 1. Re: Add another calculated row in the text table

Will This Work?

Do separate Calcs for 2018 and 2019 and Substract;

2018

(If Date=2018 THEN[Sale] end)

2019

(If Date=2019 THEN[Sale] end)

Thanks

Deepak

If it Helps, PL mark it Helpful and CORRECT to Close Thread

• 2. Re: Add another calculated row in the text table

and to get it exactly as your screenshot:

Steps:

• Write this Calc to add a new  Text Row

IF Not Contains([Table Name],"#") THEN STR([Date]) else "Difference" END

and Second Calculation for your Sale and Difference:

If ATTR([ Date])<>"Difference" THEN SUM([Sale]) else (LOOKUP(SUM(Sale),-1)-LOOKUP(SUM(Sale),-2)) ENd

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECt to Close Thread

• 3. Re: Add another calculated row in the text table

Hi Deepak,

Your output looks great. However, I am a little confused with the first step. This is what my table name is

When I created a calculated field as "IF Not Contains([Tabelle1],"#") THEN STR([Date]) else "Difference" END" It does not work

One other question, for your column, it is "sale“ instead of sale. Do you also change that column or are these two the same?

Thanks,

Abby

• 4. Re: Add another calculated row in the text table

Thanks

Deepak

If it Helps, Pl Mark it Helpful and CORRECT to Close Thread

• 5. Re: Add another calculated row in the text table

Hi Deepak,

That absolutely makes sense to me. But just out of curiosity, what if I only want the dashboard to show the difference? Something like this:

Apple 100

Banana 70

Pineapple 50

So only the difference of these names will be shown as a table. Any way I could achieve that?

Thanks,

Abby

• 6. Re: Add another calculated row in the text table

You can filter for Difference in the Date Field.

• 7. Re: Add another calculated row in the text table

Hi Abby,

Thanks

Deepak

1 of 1 people found this helpful
• 8. Re: Add another calculated row in the text table

Hi Abby,

Thanks

Deepak

• 9. Re: Add another calculated row in the text table

Hi Deepak,

Sorry for not marking this as I am stuck in one middle part and still haven't figured it out. The date field you created has three values 2018,2019 and difference. However, I could not get the difference row.

This is my dataset, the two sheets that start with STD has 2018 data and the two sheets that start with sheet has 2019 data. (STD Syndie Rank and STD Syndie Rank 1 is exactly the same and Sheet11 and Sheet 12 are exactly the same). First, I union  Syndie Rank and Sheet11 to get combine the data together, with a full list of sales in both year 2018 and 2019. In your example, you union the dataset with itself in the first step. However, my complete dataset is obtained by union two tables. So in order to do as what you did, I unioned the two complete datasets together (2*2). (There are two sheets unioned for a complete dataset, so union with the complete dataset give me 4 sheets)

And then I followed your step to create a calculated field:

However, I checked that there is no "difference" in my calculated field. Do you mind pointing out which part I did wrong?

Thanks

• 10. Re: Add another calculated row in the text table

Sorry..I can only help upto a certain point  to solve the question which  I already did. So, you may think to start another Thread to get more help from community, but it is not a good practice to keep a thread open even when the question is anwered...

• 11. Re: Add another calculated row in the text table

I figured that out! Thanks!