topic Re: MonthStartDate in QlikView

sum MonthStartDate to selected date

Tue, 21 Apr 2015 09:23:19 GMT

Hi,

I have a requirement where if user selects a date he should be able to see sum of sales from month start date to till selected date.

Ex:- If in filter user has selected 21-04-2015 then he should be able to see sales from 01-04-2014 to till date.

I tried below expression but not getting desired result. Please suggest the solution.

sum({<TargetMonth=,TargetYear=,DivName=,KeyDescription=,

ProductName=,SubCatName=,ProductCategory=,ProductNo=,FiscalYear ={$(vCurrentYear)},

SaleDate={">=$(=monthstart(GetFieldSelections(SaleDate)))"}

SaleDate={"<=$(=GetFieldSelections(SaleDate))"}

>}ActualQuantity)

Regards,

Navdeep

Re: MonthStartDate

ali_hijazi — Tue, 21 Apr 2015 09:25:33 GMT

you need to ignore selection on SaleDate

so add the following to your set analysis SaleDate =

Re: MonthStartDate

datanibbler — Tue, 21 Apr 2015 09:27:31 GMT

Hi Navdeep,

well, when the user has selected a date (and it's in proper date_format), you should be able to use the Monthstart() function on it to get that month's starting_date. Try that on its own first (good practice, always test the individual elements of a formula).

=> Then you can just use a formula, even without set_analysis, like

>> SUM(IF([sale_date] >= Monthstart[selected_date], [sales_amount], 0) <<

There won't be any sales for dates in the future, so you don't have to query that side.

HTH

P.S.: Set_analysis is a great tool and you can do cool things with it. But it's quite resource-intensive, so IMHO you should try to do without if possible.

Re: sum MonthStartDate to selected date

Anonymous — Tue, 21 Apr 2015 09:34:40 GMT

Hi,

Try

sum({<TargetMonth=,TargetYear=,DivName=,KeyDescription=,

ProductName=,SubCatName=,ProductCategory=,ProductNo=,FiscalYear ={$(vCurrentYear)},

SaleDate={">=$(=monthstart(GetFieldSelections(SaleDate)))<=$(=GetFieldSelections(SaleDate))"}

>}ActualQuantity)

Regards,

Greeshma

Re: sum MonthStartDate to selected date

Tue, 21 Apr 2015 09:41:43 GMT

Thank you all for your valuable time.

This is the other option I tried and working

=sum({

....... SaleDate={">=$(=monthstart(GetFieldSelections(SaleDate)))"}

<........SaleDate={"<=$(=GetFieldSelections(SaleDate))"}

>}ActualQuantity)