topic Re: Set analysis problem in QlikView

Set analysis problem

munna1317 — Tue, 09 Sep 2014 11:11:00 GMT

hi QV,s

Can you plz Tell me this Expression in Set analysis

I have CMDBCI table.

Req:I want count of CmDBCInames for current month/Count(CmdbCi Names till Previous month)

Thxs in advance

harish

Re: Set analysis problem

MK_QSL — Tue, 09 Sep 2014 11:23:46 GMT

What is the format of your date field?

Re: Set analysis problem

munna1317 — Tue, 09 Sep 2014 11:29:27 GMT

hi manish,

format of your date field:DD-MM-YYYY

Re: Set analysis problem

MK_QSL — Tue, 09 Sep 2014 11:41:35 GMT

Have you created a Master Calendar?

Also,

What is the meaning of till Previous Month? You want starting from your first date from database to previous month end?

Count(CmdbCi Names till Previous month)

Please clarify little more on above two questions, which will make easy to answer.

Re: Set analysis problem

munna1317 — Tue, 09 Sep 2014 11:54:11 GMT

you are Correct,I want starting from my first date of database to previous month end.

I have created Master calendar

Re: Set analysis problem

MK_QSL — Tue, 09 Sep 2014 11:57:45 GMT

Can you provide sample data base or apps?

Re: Set analysis problem

MK_QSL — Tue, 09 Sep 2014 12:00:42 GMT

For Previous Month End

=SUM({<TransactionDate = {'<=$(=MonthEnd(AddMonths(Today(),-1)))'}>}Amount)

For Current Month

=SUM({<Month = {'$(=Month(Today()))'},Year = {'$(=Year(Today()))'}>}Amount)

You can change the field names according to your application...

Re: Set analysis problem

christian77 — Tue, 09 Sep 2014 12:09:25 GMT

Hi Manish

It is simpler

MonthStart(Today())-1

than

MonthEnd(AddMonths(Today(),-1))

results are the same

salutes

Re: Set analysis problem

munna1317 — Wed, 10 Sep 2014 06:27:11 GMT

Hi Manish,

The Expression for Prevous Month is giving only Previous month Count.I need total count ( from starting date of my database )

Expression for Previous Month:

=Count({<[Fiscal Month]={'<=$(VarPrevFinMonth)'>}cmdbci_name) it is not Working.

Regards,

Harish

Re: Set analysis problem

PrashantSangle — Wed, 10 Sep 2014 06:35:09 GMT

Hi,

Expression for previous Month End:

=Count({<[Fiscal Month]={"<=$(=VarPrevFinMonth)">}cmdbci_name)

Regards

Re: Set analysis problem

munna1317 — Wed, 10 Sep 2014 06:40:53 GMT

hi,

It is not working .It results 0 value

Re: Set analysis problem

PrashantSangle — Wed, 10 Sep 2014 06:53:07 GMT

Hi,

check in text box what is your variable VarPrevFinMonth evaluating?

Sometime it convert your date to number

also check Your Fiscal Month format in list box

Regards

Re: Set analysis problem

munna1317 — Wed, 10 Sep 2014 07:20:23 GMT

Hi Max,

I have checked VarPrevFinMonth is evaluating in text box .Month Format is MMM(Jan)

and Fiscal month format also same MMM(Jan)

Re: Set analysis problem

PrashantSangle — Wed, 10 Sep 2014 07:55:57 GMT

Hi,

If possible post your sample apps?

One more thing if you want last month then add Year Field also in your set analysis.

Like,

Count({<[Fiscal Month]={"<=$(=VarPrevFinMonth)",[Fiscal Year]={"<=$(=VarYear)">}cmdbci_name)

Regards

Re: Set analysis problem

MayilVahanan — Wed, 10 Sep 2014 08:20:28 GMT

Instead of using month field, try with date field in set analysis for calculating from start date to end of previous month.

Re: Set analysis problem

munna1317 — Wed, 10 Sep 2014 08:42:49 GMT

hi ,

Can u please tell me how to get data till Previous Month end.

do you have any example Expressions or Conditions?

Regards,

Harish

Re: Set analysis problem

munna1317 — Wed, 10 Sep 2014 09:42:35 GMT

I am using this Date field,but still i m getting 0 value

=Count({<CIDate={">=$(=DATE(MakeDate(2007,01,01),'DD-MM-YYYY')))<=$(=Date(Max(AddMonths(CIDate,-1)),'DD-MM-YYYY'))"} >}cmdbci_name)

Re: Set analysis problem

MayilVahanan — Wed, 10 Sep 2014 09:57:42 GMT

Expression looks correct..

Did u please confirm that CIDate is also "DD-MM-YYYY" format or its in "MM-DD-YYYY" format?

Re: Set analysis problem

munna1317 — Wed, 10 Sep 2014 10:02:16 GMT

hi,

CIDate format is 'DD-MM-YYYY'

Re: Set analysis problem

MayilVahanan — Wed, 10 Sep 2014 10:08:17 GMT

Can you provide a sample data in excel file ?