Bar Chart issue

[Noor51]

Hi Everyone, 

In Qlik Sense on prem may 2025, I have a bar chart with an dimension called customer and a measure which is a extrap of 3 month. I have a problem in that the bar chart doesn't show most customer extrap correctly unless you select that customer individual. I been stuggling with this for a month and no AI model has help me on this. Some of the OPP_CLOSEDATE_v2 is in the future and there is one OPP_CLOSEDATE_v2 per customer. 

Here is the fields in the load script.

date((right([CALENDAR_YEAR_MONTH],2)&'/'&'1'&'/'& left([CALENDAR_YEAR_MONTH],4)),'M/D/YYYY') as Saledate,

date(OPP_CLOSEDATE,'MMM-YYYY') as CLOSE_DATE,

Date(OPP_CLOSEDATE, 'M/D/YYYY') as  OPP_CLOSEDATE_v2,

Here is the set expression in the bar chart.

If(

  NetWorkDays(MonthStart(OPP_CLOSEDATE_v2), today()) < 60,

  // --- Core for partial period: average per WD scaled to 66 ---

  Sum({<

        Saledate={"=Saledate>= MonthStart(OPP_CLOSEDATE_v2) and Saledate <= today()-1"}      >} Num#([Contract Sales]))

  / NetWorkDays(MonthStart(OPP_CLOSEDATE_v2), today()-1) * 60,

  // --- Otherwise: last 3 calendar months through the month of Max(Saledate) ---

  Sum({<

        Saledate={"=Saledate >= AddMonths(MonthStart(today()-1),-2) and Saledate <= MonthEnd(today()-1)"}

      >} Num#([Contract Sales]))

)

[Oleg_Troyansky]

Hi @Noor51 ,

I think the formula breaks right in the beginning, at the IF condition. With Customer as a Dimension, I believe you might have more than one possible value of OPP_CLOSEDATE_v2, which makes the condition fail every time. 

My recommendation would be to calculate some of these conditions in the load script and to assign the results to conditional flags, and then to formulate a much more simplified Set Analysis condition that captures all the conditions at once.

Allow me to invite you to my Qlik Expert Class that I'll be teaching in Vienna, Austria on September 22-24. I will be teaching advanced data modeling, along with advanced scripting, performance optimization, and advanced aggregation and Set Analysis techniques. You will learn the most advanced Qlik methodologies that will help you solve tough problems like this one.

Cheers,

Oleg Troyansky

[Noor51]

There is only OPP_CLOSEDATE_v2  for each customer.  We think its this part of the code that isn't work after breaking up the set expression.

Saledate={"=Saledate>= MonthStart(OPP_CLOSEDATE_v2) and Saledate <= today()-1"}   

[BrunPierre]

Perhaps the issue might be the date formatting, so try this.

Saledate={">=$(=Date(MonthStart(Date#(OPP_CLOSEDATE_v2, 'MM/DD/YYYY')), 'MM/DD/YYYY'))<=$(=Date(Today()-1, 'MM/DD/YYYY'))"}

[Noor51]

It's show the customer but not the right number. I update the OPP_CLOSEDATE_v2  to this to match the Saledate format, as FYI,  Date(MonthStart(OPP_CLOSEDATE), 'M/D/YYYY') as OPP_CLOSEDATE_v2.

[Oleg_Troyansky · Accepted solution]

Advanced Search, which was used in this part of Set Analysis, shouldn't be sensitive to date formatting. There must be another issue somewhere in the data.

Again, I think it's about multiple values of OPP_CLOSEDATE_v2 - this is why the formula works when one customer is selected, and it doesn't work with multiple customers in scope.

This advanced search condition:

Saledate={"=Saledate>= MonthStart(OPP_CLOSEDATE_v2) and Saledate <= today()-1"}   

contains a calculation that is evaluated for each distinct value of SalesDate - NOT for each single customer, as you may expect just because your chart has Customer as a dimension. Advanced Search conditions are evaluated at the level of the corresponding field, disregarding of your chart dimensions.

This is most certainly the problem. What I'd do in this situation is the following:

1. In the data load script, calculate a conditional flag, based on this condition, for each Saledate and customer. Assign 1 to those transactions that fit the condition, and 0 to those transactions that don't. Call it _Saledate_Flag, for example.

2. Replace the advanced search above with a simple condition like this:
_Saledate_Flag = {1}

This will select all the transactions that fit the criteria, for all customers, and the chart will work as expected.

Cheers,

Oleg Troyansky

[Noor51]

Can you help me with the load script or rough out line of it? 

Here is what I have so far 

Floor(MakeDate(Left(CALENDAR_YEAR_MONTH,4), Right(CALENDAR_YEAR_MONTH,2), 1)) as SaleDateNum, // Added GV

Floor(MonthStart( Alt(OPP_CLOSEDATE, Date#(OPP_CLOSEDATE,'YYYY-MM-DD')) )) as OPP_CLOSEDATE_MS_Num, // Added GV

If(not IsNull( Alt(OPP_CLOSEDATE, Date#(OPP_CLOSEDATE,'YYYY-MM-DD')) )
and SaleDateNum >= Floor(MonthStart( Alt(OPP_CLOSEDATE, Date#(OPP_CLOSEDATE,'YYYY-MM-DD')) ))
and SaleDateNum <= $(vTodayMinus1),
1, 0) as _Saledate_Flag,

[Oleg_Troyansky]

Your script looks right. What do you need help with?