topic Re: Set analysis: curent month sales in QlikView

Set analysis: curent month sales

Thu, 13 Feb 2014 16:21:06 GMT

Hi;

I need to get the sum of Salesfor the current Month.

So if I look at the data today, I want to get the current month's data,to calculate a KPI that mean :

Sales of february 2014 * 25/ Number of day

Number of day: from start day of current month to today.

That mean "sales since the start of the current month *25/ 13 ( exemple for today).

I don't want to change sum sales every one I select a date from calendar to see another indicator.

I am using the following set analysis but it doesn't seem to be working .

any suggestions???

Re: Set analysis: curent month sales

Thu, 13 Feb 2014 16:48:43 GMT

Hi,

I believe it would be something like this:

sum({1<Month={$(=Month(Today())}>} [Sales Field]/Day(Today()))

Hope it helps!

Re: Set analysis: curent month sales

luis_pimentel — Thu, 13 Feb 2014 16:50:55 GMT

=sum({<Month={$(=Month(Today())}>} [yoursalesfield]) * 25 / Day(Today())

Re: Set analysis: curent month sales

MK_QSL — Thu, 13 Feb 2014 16:52:37 GMT

=SUM({<[Invoice Date] = {'<$(=Today())'}, InvoiceMonth = {'$(=Month(Today()))'}>}SalesField) * 25/Day(Today())

Re: Set analysis: curent month sales

Fri, 14 Feb 2014 09:11:52 GMT

HI;

Please do you have an exemple qvw ??

I try all your answer but it dosen't work .

Thnks again.

Re: Set analysis: curent month sales

MK_QSL — Fri, 14 Feb 2014 09:14:36 GMT

Better if we have your QVW so that we can give you answer on the same.

You can attach a sample file or an excel file with reduced or dummy data.

Re: Set analysis: curent month sales

MK_QSL — Fri, 14 Feb 2014 09:47:09 GMT

Please check enclosed file which will show you sample data and your requested result.

Re: Set analysis: curent month sales

Fri, 14 Feb 2014 10:08:31 GMT

Thank You Manish;

It Works , Just I add InvoiceDATE = {'$(=DATE(Today()),InvoiceYEAR = {'$(=YEAR(Today()))'} to have no changes when I select onather Year and day for another KPI .

ThnKS

Re: Set analysis: curent month sales

MK_QSL — Fri, 14 Feb 2014 10:13:19 GMT

Glad that my help solved your problem.

Re: Set analysis: curent month sales

Fri, 14 Feb 2014 10:44:27 GMT

Please, just one thing: that function doesnot take into account this Day ; when I select 14/feb/2014 , I have 0 in all KPI for that function and when I add InvoiceDAY = {'$(=DAY(Today()), that does not work .

How can I solve this please?

Re: Set analysis: curent month sales

MK_QSL — Fri, 14 Feb 2014 10:54:01 GMT

Where are you using the above mentioned InvoiceDay?

Let me have the full expression you used.

Re: Set analysis: curent month sales

Fri, 14 Feb 2014 10:59:52 GMT

=((SUM({<[SINVOICEV.INVDAT_Date] = {'<$(=Today())'},YEAR = {'$(=YEAR(Today()))'}, MONTHNAME = {'$(=Month(Today()))'}>}SINVOICED.SALES)* 25/Day(Today())

This is all the function, when I select the Day 14 of this month, I have '0' as ressult. ( I have load my data today)

Re: Set analysis: curent month sales

MK_QSL — Fri, 14 Feb 2014 11:21:39 GMT

Try this....

SUM(

[SINVOICEV.INVDAT_Date] = {'<$(=Today())'},

YEAR = {'$(=YEAR(Today()))'},

MONTHNAME = {'$(=Month(Today()))'}

SINVOICED.SALES)

* 25/Day(Today())

Re: Set analysis: curent month sales

Fri, 14 Feb 2014 11:38:56 GMT

Any selected Date >13 of a month , the result will be ' 0' .

Re: Set analysis: curent month sales

MK_QSL — Fri, 14 Feb 2014 11:47:54 GMT

If you want your data should give you result based on Selected Date... Use below

This will give you sum of Sales for

- Current Year i.e. 2014

- Current Month i.e. Feb

- Less Than Selected Date i.e. any Invoice Date you select

If you select any date of January, you will get result 0 as InvoiceMonth is assigned to current month.

=SUM({<[Invoice Date] = {'<$(=Max([Invoice Date]))'}, InvoiceMonth = {'$(=Month(Today()))'}>}Sales) * 25/Day(Today())