Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
hermilogayosso
Contributor
Contributor

Using variable with set analysis (YTD)

Hello community, good day.

I am working on the following challenge:

The idea is to generate a bar graph in which the sales per month of the current year are shown vs the sales per month of the previous year: to this point there is no big problem, In the example I am comparing 2016 (pink) vs 2017 (blue):

 
 

my problem starts when I want to use a variable within the graph, it shows "Venta" or "Venta20%" (sales * 0.2) according to the selection (dimension)

vMontoVenta IF(SalesNum=1,SUM(Monto_Venta_20),SUM(Monto_Venta))

2_ytd_variable.png

 

what I need is to add that variable to the graph, so that it responds to the filter called "KPI". I have tried it as follows but without any result:

if(GetSelectedCount([Año])<>1,
Sum({<[Año]={$(=max([Año]))} >} '$ (vMontoVenta)')/1000,
Sum({<[Año]={$(vAño)}>} '$ (vMontoVenta)')/1000
)

when using that set analysis the graph shows like this

3_ytd_variable.png

Any idea what I am doing wrong ?, I appreciate your help

Labels (4)
3 Replies
vishalarote
Partner - Creator II
Partner - Creator II

Hi Hermilogayosso,

If you passed '=' sign while creating variable the use variable name as it is without any '$(variable)'

Or else 

Remove '=' sign from variable expression and used your final expression '$(variable)'

Or else 

='$(=IF(SalesNum=1,SUM(Monto_Venta_20),SUM(Monto_Venta)))' into variable vMontoVenta and pass into final expression vMontoVenta

as like if(GetSelectedCount([Año])<>1,
Sum({<[Año]={$(=max([Año]))} >} vMontoVenta)/1000,
Sum({<[Año]={$(vAño)}>} vMontoVenta)/1000
)

hermilogayosso
Contributor
Contributor
Author

Hi Vishalarote, thanks for the help
I have this variable called vMontoVenta:

= IF (SalesNum = 1, SUM (Amount_Sale_20), SUM (Amount_Sale)

and this measure:

if(GetSelectedCount([Año])<>1,
Sum({<[Año]={$(=max([Año]))} >} vMontoVenta)/1000,
Sum({<[Año]={$(vAño)}>} vMontoVenta)/1000
)

but when using them in a graph it doesn't show data

 

4_ytd_variable.png

Any other idea why it doesn't work?

vishalarote
Partner - Creator II
Partner - Creator II

Hi Hermilogayosso,

can you try this hope so it will help you,

vMontoVenta1:
IF (SalesNum = 1, SUM ({<[Año]={"$(=max([Año]))"} >}Amount_Sale_20), SUM ({<[Año]={"$(=max([Año]))"} >}Amount_Sale))

vMontoVenta2:
IF (SalesNum = 1, SUM (Amount_Sale_20), SUM (Amount_Sale))


Final Expression:

if(GetSelectedCount([Año])<>1,
$(vMontoVenta1)/1000,
$(vMontoVenta2)/1000
)