Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
simonqv16
New Contributor III

YTD Sales of selected persons

Hi,

I am trying to build a table showing year-to-date total sales amount of each salesperson who are still with the company.  Does anyone know how to do it on Table object (not in data load editor)?   Thanks.

Data table:

MonthSalespersonSalesperson StatusMonthly Sales Records
JanAWith the company

Record 1 :   500

Record 2 :   1000

JanBWith the company

Record 1 :  600

Record 2 :  800

JanCWith the company

Record 1:   301

Record 2 :   450

FebALeft the companyNIL
FebBWith the company

Record 1 :  600

Record 2 :  700

FebCWith the company

Record 1 :  450

Record 2 :  650

Expected Table object (as of Feb) :

Salesperson           Year-to-date Sales

B                             2700

C                             1851

Total                       4551

11 Replies

Re: YTD Sales of selected persons

A single cell of your table include two values? Record1 and Record2? or is this in different rows?

simonqv16
New Contributor III

Re: YTD Sales of selected persons

In the data model,  record 1 and 2 are representing different product, thus they are in different rows.  Take the first row of the table to rewrite as below:

Month       Salesperson              Salesperson Status                   Sales                Product

Jan               A                                With the company                 500                    T-shirt

Jan               A                                With the company                 1000                  Jean

rahulpawarb
Valued Contributor III

Re: YTD Sales of selected persons

Hello Simon,

I have considered that you have Date field to track the sales transaction. I have prepared below logic to populate YTD Sales per Sales Rep (First Add Sales Person dimension and then add below expression).

Sum({<Year=, Quarter=, Month=, Date={">=$(=YearStart(Max(Date))) <=$(=Max(Date))"}>}Sales)

Hope this will be helpful.

Regards!

Rahul

Re: YTD Sales of selected persons

So since A left the company in Feb, you won't show any sales?

May be like this

If(not WildMatch(Only({<MonthYear= {"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>}[Salesperson Status]), '*Left*'), Sum({<MonthYear= {"$(='>=' & Date(YearStart(Max(MonthYear)), 'MMM-YYYY') & '<=' & Date(Max(MonthYear), 'MMM-YYYY'))"}, Month, Year, Date>} Sales)

I am assuming that you have create a MonthYear field in the script using a logic like this

LOAD Date(TempDate) as Date

          Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear

          ...

Highlighted
simonqv16
New Contributor III

Re: YTD Sales of selected persons

Thank you for your help.

Master Calendar is already built and the YTD sale is successfully calculated based on the srcipt provided.  However, those  " left the company"  salespersons still listed in the report.  When I tried to just list out those still-with-company salespersons by using their "status" at the latest month, the  YTD sale figures changed back to that single month's total.    Is there any way to avoid it?

Re: YTD Sales of selected persons

Would you be able to share a sample to show the issue?

simonqv16
New Contributor III

Re: YTD Sales of selected persons

In the attached example, I tried to show the latest Active Salesperson only by using some MTD/YTD flag.  Thanks.

laddu_927
Valued Contributor

Re: YTD Sales of selected persons

You are using MTD Flag in the expression which is changing the value to month level. You should have used YTD =1 to get the YTD value according to your logic.

simonqv16
New Contributor III

Re: YTD Sales of selected persons

Thanks Shiva.

When YTD is used, the Inactive salesperson is on then on the list.  I need those Inactive salesperson off the list while showing YTD figures.