4 Replies Latest reply on Oct 7, 2018 9:13 PM by meenu choudhary

# Show sum based on current week

Hi All,

I'm, building a dashboard that would need to be refreshed everyday. I have data set with following columns [Sales id],[date]. I have target of 200 sales/week.

Ultimately, I want (1 - count(# of sales in this week)/200) * 100 -> gives me percentage of sales to be achieved by this week. Kindly help as I'm a newbie to the tool with very minimal experience.

• ###### 1. Re: Show sum based on current week

Hello,

Are you looking for something like this.

1. Current week = DATETRUNC('week',TODAY())

2. current week sales count= COUNTD(if DATETRUNC('week',[Date])=[current week] then ([Sales]) END)

3. % = (1 - ([current week sales count])/200)

Right click on % -> Default Properties - > Number Format - > Percentage

You will get the below result :

1 of 1 people found this helpful
• ###### 2. Re: Show sum based on current week

Hey thanks! It was super helpful and it perfectly suited my need.

But for my further analysis, I'm trying to have calculated fields with just the sales values and not their count. If I follow the same syntax I'm getting the following error. Cannot mix aggregated and non aggregated arguments within this argument. The calculated field I'm trying to achieve is as follows:

3_days_percent:

IIF([Date]=[Current day],([Rem_Sales]),IIF([Date]=[Current day+1],([Rem_Sales_1]),IIF([Date]=[Current day+2],([Rem_Sales_2]),NULL))).

• ###### 3. Re: Show sum based on current week

Hi Srinath,

If the post helped you to solve your query please mark it as CORRECT , so as to close the thread.

• ###### 4. Re: Show sum based on current week

Hello,

What's the logic for Rem_Sales,Rem_Sales_1 and Rem_Sales_2.

You can refer below article on what is this error and how to fix this.

https://kb.tableau.com/articles/issue/error-cannot-mix-aggregate-and-non-aggregate-arguments-with-this-function-when-creating-a-calculated-field