Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
zagzebski
Contributor

Choosing Max Month and YTD

My Data:

Customer NameMonthYearCummPerc CubeCommission
A0120120.751000
A0220120.802000
A0320120.853000
B0120120.80250
B0220120.85500
B0320120.90750
A0120130.652000
A0220130.754000
A0320130.956000
B0120130.801000
B0220130.852000
B0320130.903000

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 NameCummPerc CubeCommission
A0.803000

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

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Choosing Max Month and YTD

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

View solution in original post

5 Replies
Partner
Partner

Re: Choosing Max Month and YTD

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

View solution in original post

zagzebski
Contributor

Re: Choosing Max Month and YTD

Jason -

Awesome! I think this will work great. Thanks

Steve

Partner
Partner

Re: Choosing Max Month and YTD

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

zagzebski
Contributor

Re: Choosing Max Month and YTD

Jason -

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

Steve

Partner
Partner

Re: Choosing Max Month and YTD

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