topic Re: SET Analysis expression help in QlikView

SET Analysis expression help

Mon, 27 Oct 2014 11:48:25 GMT

I want to count how many items I got in stock this month compared to what was in stock at the same time last year.

So something like this:

( {<MONTH={$(vCurrentYearMonth)},MONTH = {$(vLastyearMonth)},ItemNumber={'76319','76329'} >} Items) plus the big problem

I want to count this month 27 days of oktober 2014 and 27 days oktober 2013

Re: SET Analysis expression help

Mon, 27 Oct 2014 11:59:02 GMT

Hi,

This year i mean 2014 oct upto 27 days Count of Items

Count({<month={'Oct'}, Day={'>=1 <=27'},Year={2014}>} Items)

Previous year i mean 2013 oct upto 27 days Count of Items

Count({<month={'Oct'}, Day={'>=1 <=27'},Year={2013}>} Items)

Re: SET Analysis expression help

Mon, 27 Oct 2014 12:03:27 GMT

Hi Rakesh

Ok yes that would work, - but the amount of days is variable.

If you use the variable vCurrentyearMonth on the 22. oktober or any other date the amount of days differ

Re: SET Analysis expression help

Mon, 27 Oct 2014 12:13:48 GMT

you can derive from the date as a day and as a month and as a year then u will get it right.

Re: SET Analysis expression help

Mon, 27 Oct 2014 12:34:38 GMT

I not sure

I want to compare 2014 to 2013

so if I use a variable to set current month I get 27 days

But If i use a variable to set month last year i get 31 days

I need to compare the same amount of days as days in current month this year

Re: SET Analysis expression help

PrashantSangle — Mon, 27 Oct 2014 12:51:39 GMT

Hi,

Can you post your expression of variable

Regards

Re: SET Analysis expression help

chematos — Mon, 27 Oct 2014 18:08:18 GMT

Set this variable:

vCurrentDays = Day(Today())

Current Year:

Count({<Month={$(vCurrentYearMonth)}, Day={<=$(vCurrentDays)},Year={$(=only(Year))},ItemNumber={'76319','76329'} >} Items)

Previous Year:

Count({<Month={$(vCurrentYearMonth)}, Day={<=$(vCurrentDays)},Year={$(=only(Year)-1)},ItemNumber={'76319','76329'} >} Items)

Re: SET Analysis expression help

Tue, 28 Oct 2014 09:40:23 GMT

Hi JOSE

I got a problem with the vCurrentDays variable

Count({<Month={$(vCurrentYearMonth)}, Day={'>=1 <=$(vCurrentDays) '},Year={$(=only(Year))},ItemNumber={'76319','76329'} >} Items)

First of all – Day is numeric, so I removed the quoatationsigns surrounding {'>=1 <=$(vCurrentDays) '} to {>=1 <=$(vCurrentDays) } so far so good.

But removing the quotations makes a fault later on after {>=1 <=$(vCurrentDays )},

Regards

Søren

Fra: JOSE MARIA TOS MIÑANA

Sendt: 27. oktober 2014 19:09

Til: Schou, Hans Henrik Søren (SSC009)

Emne: Re: - SET Analysis expression help

Qlik Community<http://community.qlik.com/>

SET Analysis expression help

reply from JOSE MARIA TOS MIÑANA<http://community.qlik.com/people/chema.tos?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/642473?et=watches.email.thread#642473>

Re: SET Analysis expression help

alkesh_sharma — Tue, 28 Oct 2014 09:49:54 GMT

Hello,

Try these Expression:

Curent Year: SUM({<fYear={$(vMaxYear)},fMonth={$(vMaxMonth)},[%Master Date]={"<=$(='$(vToday)')"} >}[Net value])

Previous Year: SUM({<fYear={$(vPreviousYear)},fMonth={$(vMaxMonth)},[%Master Date]={"<=$(='$(vlastMaxDate)')"}>}[Net value_NETWR])

vMaxYear:=max(fYear)

vMaxMonth: =max(fMonth)

vToday: =date(Today(),'DD-MM-YYYY')

vLastMaxDate: =date((Today()-365),'DD-MM-YYYY')

Re: SET Analysis expression help

Tue, 28 Oct 2014 10:01:01 GMT

Hi alkesh

max(fyear) - what is fYear - I get an error using that

Re: SET Analysis expression help

alkesh_sharma — Tue, 28 Oct 2014 10:05:14 GMT

I used fYear because I had fYear as the Year filed, you use you Year field and correspondingly replace all fields with your relevant fields.

Re: SET Analysis expression help

Tue, 28 Oct 2014 10:09:04 GMT

OK I tried that OK

What is %Master Date

Re: SET Analysis expression help

alkesh_sharma — Tue, 28 Oct 2014 10:28:28 GMT

Its the date field.

Re: SET Analysis expression help

alkesh_sharma — Tue, 28 Oct 2014 10:31:24 GMT

I had the same requirement for my client and after a lot of research, I finally created the expressions which I shared.

You just need to tweak it a little to fit in with your data set.

Re: SET Analysis expression help

Tue, 28 Oct 2014 11:13:17 GMT

I´m trying - but not beeing especially good with the syntaxes I run into trouble like this

Where the expression dies and i cant find the error - even Expression is OK

Re: SET Analysis expression help

chematos — Tue, 28 Oct 2014 11:19:48 GMT

What is the problem with the variable? Have you added the variable as the other ones you have?

You could set the expression without the variable and see if it works, you need to have Day field as numeric

vCurrentDays = Day(Today())

Current Year:

Count({<Month={$(vCurrentYearMonth)}, Day={<=$(=Day(Today())},Year={$(=only(Year))},ItemNumber={'76319','76329'}>} Items)

Previous Year:

Count({<Month={$(vCurrentYearMonth)}, Day={<=$(=Day(Today())},Year={$(=only(Year)-1)},ItemNumber={'76319','76329'} >} Items)

Re: SET Analysis expression help

alkesh_sharma — Tue, 28 Oct 2014 11:23:57 GMT

Try This:

Count({<AAR= {$(vMaxYear)}, MAANED = {$(vMaxMonth)}, DATE = {"<=$(='$(vToday)')"} >}

You made a syntax error, " " are missing..

Re: SET Analysis expression help

Tue, 28 Oct 2014 12:57:11 GMT

No I got the ”” but still get an error or no data

Using the “ ” and the ‘ ‘ around the vToday makes it searching for a text field, - right?

Fra: Alkesh Sharma

Sendt: 28. oktober 2014 12:24

Til: Schou, Hans Henrik Søren (SSC009)

Emne: Re: - SET Analysis expression help

Qlik Community<http://community.qlik.com/>

SET Analysis expression help

reply from Alkesh Sharma<http://community.qlik.com/people/alkesh.sharma?et=watches.email.thread> in App Development - View the full discussion<http://community.qlik.com/message/643101?et=watches.email.thread#643101>

Re: SET Analysis expression help

Tue, 28 Oct 2014 12:59:36 GMT

You may not see it because of the color but vToday is not recognized as a variable

Fra: Alkesh Sharma

Sendt: 28. oktober 2014 12:24

Til: Schou, Hans Henrik Søren (SSC009)

Emne: Re: - SET Analysis expression help

Qlik Community<http://community.qlik.com/>

SET Analysis expression help

Re: SET Analysis expression help

alkesh_sharma — Tue, 28 Oct 2014 14:31:10 GMT

Soren,

I guess you need to learn the qlikview basics before you go on to these complicated expression.

vToday is a Variable. you have to set a this variable and then use it in the expression..

Guess you just copies what I gave you and trying to run it. It doesn't work like this