topic Re: Sum with Set analysis for a distinct period in QlikView

Sum with Set analysis for a distinct period

Wed, 15 Jul 2015 10:37:33 GMT

Hello, guys!

I'm stuck with set analysis in my straight table.

I need to get a sum of days for the period: from the month picked by user in my "Months" filter till Dec- 2015.

For this purpose I used the following expression:

Sum({<Month_Date={">=$(=Date(addmonths(Month_Date,1),'MMM-YY')<='Dec-15'"},Month_name=>} Days)

But it's not working!

I also need sum of days for previous month ( the one before the month that picked by user in "Months" filter) :

Again the expression I used:

Sum({<Month_Date={"=$(=addmonths(Month_Date,-1)"},Month_name=>} Days)

Again the same result as the previous one.

Can anybody please help me to sort this out?

Sample app is attached.

Re: Sum with Set analysis for a distinct period

sunny_talwar — Wed, 15 Jul 2015 10:49:14 GMT

See if this is what you wanted:

Vacations current month till December

Sum({<Month_Date={"$(='>=' & Date(addmonths(Max(Month_Date),1),'MMM-YY') & '<=' & Date(YearEnd(Max(Month_Date)), 'MMM-YY'))"},Month_name=>} Days)

Prvious Month

Sum({<Month_Date={"$(=Date(addmonths(Max(Month_Date),-1), 'MMM-YY'))"},Month_name=>} Days)

Re: Sum with Set analysis for a distinct period

Wed, 15 Jul 2015 11:48:49 GMT

It doesnt seem to work properly. It doesnt sum up all the values starting from the picked month till December

Re: Sum with Set analysis for a distinct period

sunny_talwar — Wed, 15 Jul 2015 12:20:30 GMT

It would be ideal if you can let me know some selections and expected results when we make those selections. I am not familiar with the expected output.

I hope that makes sense.

Best,

Sunny

Re: Sum with Set analysis for a distinct period

settu_periasamy — Wed, 15 Jul 2015 12:34:13 GMT

Hi,

may be like this

=Sum({<Month_Date={'>=$(=Min(Month_Date))<=$(=Max({1}Month_Date))'},Month_name=>} Days)

Re: Sum with Set analysis for a distinct period

Wed, 15 Jul 2015 12:39:48 GMT

For example, if u choose July in the filter and then look the first on in the table of employee (Emp_name: Aaelyjkig Wvfq), the sum of days for this employee is 0 now. However it should be 7. The total of the column "Vacations current month till December" should be 110 for July, and now it's 35.

Re: Sum with Set analysis for a distinct period

Wed, 15 Jul 2015 12:41:25 GMT

Nope, it shows the sum of all year

Re: Sum with Set analysis for a distinct period

settu_periasamy — Wed, 15 Jul 2015 13:22:31 GMT

Hi,

Just try to change Min(Month) to Max(Month).

=Sum({<Month_Date={'>=$(=Max(Month_Date))<=$(=Max({1}Month_Date))'},Month_name=>} Days)

Re: Sum with Set analysis for a distinct period

krishna20 — Wed, 15 Jul 2015 13:40:07 GMT

Hi ,

Please find the attached sample.It may be your desired result i think.

For Previous Month

=Sum({<Month_Date={"$(='>=' & Date(AddMonths(Max(Month_Date),-1),'MMM-YY'))"}>}Days)