How to Write a Variable for a Pie Chart with Multiple Fields

[aftabn10]

Hi, I am looking for some help in regards to writing a variable for a Pie Chart on my dashboard. I currently have the following variable for a KPI chart and this shows the latest weeks value but has the flexibility to change if a different week is selected.

=Sum({<week_date ={">=$(=Weekstart(max(week_date)))<=$(=Weekend(max(week_date)))"}>}total_calls)

Now for my pie chart, I want to show the different type of work that has been worked on during the week and this is through the 'source' column which has the following types. This will be my dimension(slice).

SOURCE
Calls
Email
Offline

The measure will show the number of 'touches' and is then displayed as a %. This works if I put the fields in, but I would like to write a variable so that it is dynamic but not sure how I do that.

Would appreciate any advice on how to move this forward.

[rwunderlich]

Why put SOURCE values in a variable?  Instead, can you just have users select SOURCE values in a filter and let the default behavior do the work?

-Rob

[aftabn10]

Hi, Rwunderlich, thank you for your response. The reason for putting Sources in the variable is that I am trying to replicate a pie chart from the following dashboard.

https://sense-demo.qlik.com/sso/sense/app/8ad4f60b-b850-4d49-bfb4-5095965d915e/sheet/jyttc/state/ana...

The only issue I have is that I want the data for the latest week so somehow need to incorporate the query from my previous post so that its dynamic.

[rwunderlich]

Leave the measure as you have coded it, that will take care of the date range.  Use SOURCE as the Dimension, that will filter the source according to the user selections. There is no need to mention SOURCE in the measure. 

-Rob

[aftabn10]

Thanks Rwunderlich. When I leave the formula as it is I get the following (table view):

SOURCE | $(vVariable)
Calls  | 21,569
Email | 21, 569
Offline | 21,569

21,569 is the sum of all 3 sources, the individual values for each is the following:

Calls  | 8,477
Email | 4,040
Offline | 9,052

but I am not sure why its showing me the sum for the column rather than the individual (sources). It seems like its not picking up the breakdown of the SOURCE even though I have selected it as the Dimension.

[rwunderlich]

What's the expression in vVariable?

Is there a link between fields SOURCE and total calls?

-Rob

[aftabn10]

The expression in vVariable is the following:

=Sum({<[w_c.week_date] ={">=$(=Weekstart(max([w_c.week_date])))<=$(=Weekend(max([w_c.week_date])))"}>}[w_c.touches])

w_c is the alias name of the table where the data is being loaded from but don't understand why I am getting a total of all sources.

[rwunderlich]

It sounds like there is no link between the SOURCE field and the w_c.touches field.  Are they linked in you model?  If you're not sure can you post a screenshot of your data model viewer?

-Rob

[aftabn10]

The source field is coming from the same table as the touches field that's why its weird. It comes as w_c.source under dimensions hence there wouldn't be a linkage as its within the same table right?

[aftabn10]

Rob, when I remove the variable and add the source and date fields as the dimension and select the specific date I then get the values that I am looking for. Attached image, its only when I am adding the variable that it doesn't work but want the variable so that its dynamic like my KPIs.  I have attached images of my data model viewer which shows the w_c table and also an image when I use the individual fields to get the data I am looking for but want this to work dynamically. Please bear in mind the example numbers from above will differ to the image attached as the weeks volume has now changed.