Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

zagzebski
Contributor

YTD Value (Set Analysis)

I am trying to get a YTD Commission. My data is below.

CustIDCustMonthDateDateNumDateYearProdCommissionCommission-YTD
V|WALLF-1|B202AFeb2/1/2012409402012B2028142.24
V|MADAR-1|B202BFeb2/1/2012409402012B2021425.19
V|WISAL-1|B202CFeb2/1/2012409402012B2021923.95
V|NATIG-1|B202DFeb2/1/2012409402012B2024186.72
V|KICKV-2|B202EFeb2/1/2012409402012B2023917.07
V|PRODL-1|B202FFeb2/1/2012409402012B202918.97
V|GARDM-1|B202GFeb2/1/2012409402012B2021799.34

What I want (in the Commission-YTD column) is sum of all months up to the month month selected for that year. (If user selects Feb and 2012, then Commission-YTD column is a total of Jan 2012 and Feb 2012 for that particular row.

My current expression is bringing back 0's:

  Sum ({<[DateNum] = {$(=<Max([DateNum]))}>}Commission)

Tags (1)
3 Replies

Re: YTD Value (Set Analysis)

Try Sum ({<[Month]=, [DateNum] = {'<=$(=Max([DateNum]))'}>}Commission)

Note, don't use Month, Date or DateNum as dimension in your chart. Or do it just to see what then happens.


talk is cheap, supply exceeds demand
zagzebski
Contributor

Re: YTD Value (Set Analysis)

Thanks! So this worked great but if I want this object to work for every month regardless of any selections how could I write the exdpression.

For example if the row has Feb 2012 data then it shows Feb 2012 YTD Commissions, If row is showing June 2013 data, then it shows June 2013 YTD Commissions, If the row is showing Nov 2011 data then it shows Nov 2011 YTD Commissions, and so on...

Re: YTD Value (Set Analysis)

In that case you can't use set analysis. The set is calculated once for the entire chart, not per row. See this document for an explanation and a solution: Calculating rolling n-period totals, averages or other aggregations


talk is cheap, supply exceeds demand
Community Browser