topic SET ANALYSIS help in QlikView

SET ANALYSIS help

Tue, 11 Nov 2014 12:18:03 GMT

I want to compare two year sales, at the same month and same date

ex.

count number of sold items in november this year and compare with sold items sold at the same time last year.

As we have 11. november I want to count number of sold items in november this year = 11 days and

compare with the same amount of days last year i november.

I´ve tried with some help from another thread with something like this

({<year={$(vPreviousYear)},month={$(vMaxMonth)}, day={$(vLastMaxDate)}, itemgroup={75211}>}itemnr)

but this doent work

Re: SET ANALYSIS help

ali_hijazi — Tue, 11 Nov 2014 12:21:36 GMT

ignore selection inside your set analysis on year and month

Year=,Month=

count({<year=,month=, year={"$(vPreviousYear)"},month={"$(vMaxMonth)"}, day={"$(vDay)"}, itemgroup={75211}>}itemnr)

Re: SET ANALYSIS help

Tue, 11 Nov 2014 12:28:07 GMT

Ali

what do you mean by ignore??

Re: SET ANALYSIS help

Tue, 11 Nov 2014 12:29:47 GMT

ignore is nothing but irrespective selections of any month (month=;)

Re: SET ANALYSIS help

Tue, 11 Nov 2014 12:36:28 GMT

Hi Ali

I was a little hasty - I see your expression but this gives me only the count for today()

I want to count for each day in november upto the 11. and the same fore last year

I tried with your example but I dont get the result I want

Re: SET ANALYSIS help

mehmet_caldak — Tue, 11 Nov 2014 12:47:08 GMT

Hi,

your current Set Expression consider just last year today, if you want to calculate for last year month to day you should use the expression below;

({<year={$(vPreviousYear)},month={$(vMaxMonth)}, day ={'<=$(vLastMaxDate)'}, itemgroup={75211}>}itemnr)

Re: SET ANALYSIS help

ali_hijazi — Tue, 11 Nov 2014 12:53:28 GMT

then remove the condition on day

count({<year=,month=, year={"$(vPreviousYear)"},month={"$(vMaxMonth)"}, itemgroup={75211}>}itemnr)

Re: SET ANALYSIS help

Tue, 11 Nov 2014 13:12:00 GMT

Hi Ali

If i remove the DAY condition I will count all the items sold in whole november last year, so I cant compare with the amount of days in november this year which is only 11 days

Re: SET ANALYSIS help

ali_hijazi — Tue, 11 Nov 2014 13:44:22 GMT

then add the Year and Month, and day as dimensions to your chart and you can drag the dimension on chart to see values of each day near the day of Nov of last year

Re: SET ANALYSIS help

Tue, 11 Nov 2014 13:46:28 GMT

go for comaparative analysis if not possible in expression

Re: SET ANALYSIS help

ali_hijazi — Tue, 11 Nov 2014 13:56:24 GMT

yes you can add alternate states and make comparative analysis in your expressions in the same chart

Re: SET ANALYSIS help

Wed, 12 Nov 2014 06:53:36 GMT

Hi Mehmet

Using DAY gives me only the amount of items sold on TODAY() thsi year and last year.

What I want is - what is sold in november this year and the same period last year equal to the first 12 days of november this year and 12 days i november last year - hope you understand

Re: SET ANALYSIS help

mehmet_caldak — Wed, 12 Nov 2014 08:06:54 GMT

Hi Soren,

just try this ,

Count ({<year={$(vPreviousYear)},month={$(vMaxMonth)},date={'<=$(vLastMaxDate)'}, itemgroup=75211}>}itemnr)

i tried this on your attached qvw, it works fine.

Re: SET ANALYSIS help

Wed, 12 Nov 2014 08:15:00 GMT

Hi MEHMET

using the expression gives me an error in the expression like this

the variable vlastMaxDate somethings is wrong and the little red wave under the end parentes???????

Re: SET ANALYSIS help

rubenmarin — Wed, 12 Nov 2014 08:17:55 GMT

Hi, you only need:

count({<year, month, date={'$(vLastMaxDate)'}, itemgroup={75211}>}itemnr)

count({<year={$(vPreviousYear)},month={$(vMaxMonth)}, day={'$(=Day(vLastMaxDate))'}, itemgroup={75211}>}itemnr)

Re: SET ANALYSIS help

mehmet_caldak — Wed, 12 Nov 2014 08:21:18 GMT

of course, itemgroup value is not within brackets sorry..

the right one is this;

Count ({<year={$(vPreviousYear)},month={$(vMaxMonth)},date={'<=$(vLastMaxDate)'},itemgroup={75211}>}itemnr)

Re: SET ANALYSIS help

Wed, 12 Nov 2014 08:26:02 GMT

Hi Ruben

the 2 expression only gives me the amount of the day and not between first day of month and lastMaxDate

which is what I want

Re: SET ANALYSIS help

jyothish8807 — Wed, 12 Nov 2014 08:32:16 GMT

Hi Soren,

try like this:

count({<year={$(vPreviousYear)},month={">=$(=monthstart(date(max(month))) )<=$(=monthend(date(max(month))))"}, itemgroup={75211}>}itemnr)

Regards

Re: SET ANALYSIS help

Wed, 12 Nov 2014 08:32:32 GMT

It works but I only get the amount for VLastMaxDate and not for the whole month

from FirstDayOfMonth to VLastMaxDate

Re: SET ANALYSIS help

rubenmarin — Wed, 12 Nov 2014 08:38:50 GMT

Ops, sorry I readed this too fast, then it should be:

count({<year, month, date={'<=$(vLastMaxDate)'}, itemgroup={75211}>}itemnr)

or Mehmet expression also will work