topic Re: Choosing Max Month and YTD in QlikView

Choosing Max Month and YTD

zagzebski — Sun, 08 Sep 2013 16:53:48 GMT

My Data:

Customer Name	Month	Year	CummPerc Cube	Commission
A	01	2012	0.75	1000
A	02	2012	0.80	2000
A	03	2012	0.85	3000
B	01	2012	0.80	250
B	02	2012	0.85	500
B	03	2012	0.90	750
A	01	2013	0.65	2000
A	02	2013	0.75	4000
A	03	2013	0.95	6000
B	01	2013	0.80	1000
B	02	2013	0.85	2000
B	03	2013	0.90	3000

I need an expression where the user selects a Year and a Month and a percent (based on a Variable) and it returns the sum of all commission for that year and all months <= Month selected, IF the total commission IF "CummPerc Cube" is <= the variable selected.

So for example if the user selects Year: 2012, Month: 02 and the Variable of .82 the the returned object would be:

Customer Name	CummPerc Cube	Commission
A	0.80	3000

because Customer A is the only one that had "CummPerc Cube" value of under .82 in the month/year that was selected.

Re: Choosing Max Month and YTD

Jason_Michaelides — Sun, 08 Sep 2013 17:37:09 GMT

There's probably a better way but this seems to work:

Sum({1<Month={"<=$(=Max(Month))"},Year=P(),[CummPerc Cube]={"<=$(=vCommission)"},[Customer Name] = P({$<[CummPerc Cube]={"<=$(=vCommission)"}>})>} Commission)

See attached.

Hope this helps,

Jason

Re: Choosing Max Month and YTD

zagzebski — Sun, 08 Sep 2013 18:47:40 GMT

Jason -

Awesome! I think this will work great. Thanks

Steve

Re: Choosing Max Month and YTD

Jason_Michaelides — Mon, 09 Sep 2013 05:43:04 GMT

You may want to remove the '1' at the start of the set analysis to keep other selections you may have.

Re: Choosing Max Month and YTD

zagzebski — Tue, 10 Sep 2013 02:46:12 GMT

Jason -

Any ideas on why it seems to only sum on the month I select...not all months <= the month selected.

Steve

Re: Choosing Max Month and YTD

Jason_Michaelides — Thu, 12 Sep 2013 09:52:09 GMT

I think it worked OK for me. Can you post your application that isn't working?