topic Need Help on Set Expression in QlikView

Need Help on Set Expression

Fri, 09 Dec 2011 13:24:09 GMT

Dear All,

Based on Customer Selection I can find the last week number of invoices and value of in rupees.

But I am generating the report on weekly basis I have a problem in suppose when the customer they don't invoices and value of rupee for last 4 weeks means its taking previous 4th week maximum of value and number of invoices, but I need to display last week as zero number of invoices and value of invoices.

Can anybody help me. below the expression is ..

= sum({<WEEK = {'$(=max(WEEK-1))'}>} NB_INVOICE)

= sum({<WEEK = {'$(=max(WEEK-1))'}>} TOTAL_RUPEE)

Regards

Chriss

Need Help on Set Expression

swuehl — Fri, 09 Dec 2011 14:03:04 GMT

Chris,

I assume that you don't use a master calendar which holds all WEEKs regardless if you got NB_INVOICE and TOTAL_RUPEE values linked to them?

So you have a field WEEK with gaps where you don't have data for?

Not sure if this is the best solution, but maybe you could replace the max(WEEK-1) with an expression that creates a WEEK value based on today() or something appropriate, maybe like

=sum({<WEEK = {'$(=week(today())-1)'} >} NB_INVOICE)

This expression (week(today()) must result in the same format as your WEEK field (I assumed integer).

BTW, how do you handle year change if using a WEEK number?

Regards,

Stefan

Need Help on Set Expression

Fri, 17 Feb 2012 11:02:54 GMT

Dear Stefan,

what you assumed is correct,

so I tried =Sum({<WEEK={"$(=max({<YEAR={"$(=max(YEAR))"}>}WEEK))-1"}>}NB_INVOICE) this expression is working fine.

Now I am facing the same old problem of if there is no data for the last 2 or three weeks it takes the value of 2 weeks before.

Like we are in 7th week there is no data for 5 and 6 it should display as 0 but its taking the 3rd week data.

Can you please help me on this.

Regards

Chriss

Need Help on Set Expression

swuehl — Fri, 17 Feb 2012 15:07:28 GMT

In your example, we are in 7th week. Do you have data for the 7th week?

If yes, your set expression

=Sum({<WEEK={"$(=max({<YEAR={"$(=max(YEAR))"}>}WEEK))-1"}>}NB_INVOICE)

should evaluate to

--> =Sum({<WEEK={"$(7-1"}>}NB_INVOICE)

--> =Sum({<WEEK={"6"}>}NB_INVOICE)

and if you don't have data for the Week 6, the result should be zero.

If you don't have data for the 7th week, no data for week 5 and 6 too, your set expression will evaluate to

--> =Sum({<WEEK={"$(4-1"}>}NB_INVOICE)

--> =Sum({<WEEK={"3"}>}NB_INVOICE)

So no wonder that you get the data for Week 3 returned.

max(WEEK) will take the max possible data into account, i.e. it is dependent on the data you have.

[BTW, I would also set the YEAR to the max(Year) to limit the NB_INVOICE records to this year, see below]

That's why I suggested to use

{'$(=week(today())-1)'} >}

instead, that should evaluate to {'6'} and should return zero in your set expression.

If you need to regard the YEAR also (what your probably need to do), use

=sum({<WEEK = {'$(=week(today())-1)'}, YEAR={'$(=year(today()))'} >} NB_INVOICE)

Try using the dollar sign expressions =$(...) each in a e.g. text box and look what is returned. Then select this number in a WEEK list box and look what is returned from a =sum( NB_INVOICE) expression.

Also creating a table box with WEEK, YEAR and NB_INVOICE could give you some insight in your data and how it is depending on selections (and set expression is just like a per-expression-selection).

Hope this helps,

Stefan

Need Help on Set Expression

Mon, 20 Feb 2012 07:25:35 GMT

Dear Stefa,

First thanks for your time explain about set analysis. the expression which is working fine...

Thanks a lot for your help.

Regards

Chriss