topic Re: Expression in QlikView

Expression

Mon, 01 Jun 2015 10:55:59 GMT

Hi Experts

I have an expression for MTD sales

Sum({< MonthYear=, Date = {'>=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'} >} Sales)

and I want to choose top 5 customers for each branch

SUM({<Customer = {"=Rank(SUM(Sales),4)<=5"}>}Sales)

How can i combine these two expressions together

Please Help

Re: Expression

sunny_talwar — Mon, 01 Jun 2015 11:00:26 GMT

Try this may be:

Sum({<MonthYear=, Date = {'>=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'}, Customer = {"=Rank(Sum({<MonthYear=, Date = {'>=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'}>}Sales),4)<=5"}>} Sales)

Re: Expression

Mon, 01 Jun 2015 11:07:46 GMT

its providing all the values as null

Re: Expression

sunny_talwar — Mon, 01 Jun 2015 11:13:19 GMT

‌Does your 2 expression give you a non-null output when used separately? I made the assumption that the two expressions work individually and they just needed to be combined somehow. Let me know if one or the other doesn't work cause then we will fix one piece at a time.

Best,

Sunny

Re: Expression

Kushal_Chawda — Mon, 01 Jun 2015 11:29:35 GMT

Try like this,

SUM({<Customer = {"=Rank(SUM({< MonthYear=, Date = {'>=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'}>}Sales),4)<=5"}>}Sales)

Re: Expression

Mon, 01 Jun 2015 11:42:20 GMT

Part of the expression is working
Here with this I Attached the QVW and the Data please Have a look

Re: Expression

Mon, 01 Jun 2015 12:06:04 GMT

HI kush

Its providing lots of null values

Re: Expression

sunny_talwar — Mon, 01 Jun 2015 12:10:32 GMT

Great, the sample would help, but in addition would you be able to let us know what your expected output looks like? For example if you select Apr 2015 what would you like to see??

Best,

Sunny

Re: Expression

Kushal_Chawda — Mon, 01 Jun 2015 12:22:41 GMT

See the attached

Re: Expression

Mon, 01 Jun 2015 12:39:33 GMT

Thanks Kush

if you add branch to Dimensions

the out put result should be by Branch Top 5 customers

Now its providing top 5 customers

Please help

Re: Expression

Kushal_Chawda — Mon, 01 Jun 2015 14:18:15 GMT

Can you paste your output here which you needed?

Re: Expression

Mon, 01 Jun 2015 14:45:40 GMT

HI Kush

Here is the QVW attached

Expected each Branch by customer top 5 sales

Re: Expression

Mon, 01 Jun 2015 15:12:13 GMT

HI this is the expected output

Expected each Branch by customer top 5 sales

Re: Expression

Kushal_Chawda — Mon, 01 Jun 2015 18:14:54 GMT

see the attached