Barchart and valuelist

[fvarlet]

Hi Qlik Sense Community!

I try to create a barchart with a Valuelist as dimension and a relatively complex measure.

If I use:
Dimension:

=ValueList('Revenues < 80% Business Plan','Business Plan respected','Revenues > 120% Business Plan')

Measure:

If(ValueList('Revenues < 80% Business Plan','Business Plan respected','Revenues > 120% Business Plan')='Revenues < 80% Business Plan'
   ,
    1
   ,
If(ValueList('Revenues < 80% Business Plan','Business Plan respected','Revenues > 120% Business Plan')='Business Plan respected'
   ,
    2
   ,
If(ValueList('Revenues < 80% Business Plan','Business Plan respected','Revenues > 120% Business Plan')='Revenues > 120% Business Plan'
   ,
    3
  )))

it works as expected.

But, instead of 1, 2, and 3, I would like to use an expression like (I've only written the code that replace '2' in the code hereabove for a easier readibility):

(...)
If(ValueList('Revenues < 80% Business Plan','Business Plan respected','Revenues > 120% Business Plan')='Business Plan respected'
   ,
    If(PeriodSelect='Last quarter',
        // Nb of Business Plans correctly estimated
        Sum(
            Aggr(
                 If(
                     Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(QuarterStart([_MonthYear],-1))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                                  >} BPL_QuarterlyInvoice_Amount
                        )
                     /
                     Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(QuarterStart([_MonthYear],-1))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                                  >} BPL_BusinessPlan_QuarterlyAmount
                        )
                     >= 0.8
                     AND
                     Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(QuarterStart([_MonthYear],-1))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                                  >} BPL_QuarterlyInvoice_Amount
                        )
                     /
                     Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(QuarterStart([_MonthYear],-1))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                                  >} BPL_BusinessPlan_QuarterlyAmount
                        )
                     <= 1.2
                    ,1
                    ,0
                   )
                 , CompanyID
                )
           )

       ,
        // Nb of Business Plans correctly estimated
        Sum(
            Aggr(
                 If(
                     Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(YearStart([_MonthYear]))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                                  >} BPL_QuarterlyInvoice_Amount
                        )
                     /
                     Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(YearStart([_MonthYear]))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                                  >} BPL_BusinessPlan_QuarterlyAmount
                        )
                     >= 0.8
                     AND
                     Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(YearStart([_MonthYear]))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                                  >} BPL_QuarterlyInvoice_Amount
                        )
                     /
                     Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(YearStart([_MonthYear]))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                                  >} BPL_BusinessPlan_QuarterlyAmount
                        )
                     <= 1.2
                    ,1
                    ,0
                   )
                 , CompanyID
                )
           )
      )
(...)

where PeriodSelect is an island table in my script and is used in a filter pane.

And in this case, only the first case of my ValueList is considered (so only one bar is drawn). If I change the order of values within the ValueList, another bar is drawn (corresponding to the new first value of my ValueList)

That's really weird and I don't know what is the best approach. In addition every change a long time before being displayed.
And unfortunately it will be quite difficult to sent an exemple here.

Do you understand what is happening here and what to do?

Thanks in advance

[sunny_talwar · Accepted solution]

You can read here why Aggr() and Synthetic dimension (ValueList) don't work together

Aggr and Synthetic Dimensions

[fvarlet]

Thank you very much, your link has helped me a lot to understand why that can not work. It remains a weird effect but I've found a way to avoid it, and even to improve it 🙂

I've decided to change the Aggr into a set analysis. The only way I've found to make it work is to write the set analysis in a single line:

Count(Distinct
      {<CompanyID={'=Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(QuarterStart([_MonthYear],-1))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}>} BPL_QuarterlyInvoice_Amount)/Sum(DISTINCT {<MonthlyInvoice_Date={">=$(=Num(QuarterStart([_MonthYear],-1))) <=$(=Num(QuarterEnd([_MonthYear],-1)))"}>} BPL_BusinessPlan_QuarterlyAmount)> 1.2'}
      >} CompanyID
     )

It works but the code is not easily readable.

How can I include end of lines within the set analysis without modifying the result?

[sunny_talwar]

---

@fvarlet wrote:

How can I include end of lines within the set analysis without modifying the result? 

---

Include end of lines? What does that mean?

[fvarlet]

I want to write the set analysis is several lines, like:

Count(Distinct
      {<CRDID={'=Sum(DISTINCT
                     {<MonthlyInvoice_Date={">=$(=Num(QuarterStart([_MonthYear],-1)))
                                             <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                     >} BPL_QuarterlyInvoice_Amount)
                 /
                 Sum(DISTINCT
                     {<MonthlyInvoice_Date={">=$(=Num(QuarterStart([_MonthYear],-1)))
                                             <=$(=Num(QuarterEnd([_MonthYear],-1)))"}
                     >} BPL_BusinessPlan_QuarterlyAmount)
                 > 1.2'}
      >} CRDID
     )

But this code doesn't work

[sunny_talwar]

I think breaking the set analysis might be causing this... try this (with some additional tweaks)

Count(Distinct
      {<CRDID={"=Sum(DISTINCT
                     {<
                       MonthlyInvoice_Date={[>=$(=Num(QuarterStart([_MonthYear],-1)))<=$(=Num(QuarterEnd([_MonthYear],-1)))]}
                     >} BPL_QuarterlyInvoice_Amount)
                 /
                 Sum(DISTINCT
                     {<
                       MonthlyInvoice_Date={[>=$(=Num(QuarterStart([_MonthYear],-1)))<=$(=Num(QuarterEnd([_MonthYear],-1)))]}
                     >} BPL_BusinessPlan_QuarterlyAmount)
                 > 1.2"}
      >} CRDID
     )

[fvarlet]

It works, thanks a lot

I tried with simple quotes instead of squared braquets, but that didn't work.
Unfortunately Qlik Sense doesn't detect my closing braquets and closing double quotes anymore, so I have to check very carefully I haven't forgotten any closing braquets/double quotes/..., but the code is quite easy to maintain now.

Thanks again!

[sunny_talwar]

Super, I am glad you were able to resolve your issue.

Best,
Sunny