topic Re: Need Support with Syntax with Set Analysis in QlikView

Need Support with Syntax with Set Analysis

Thu, 31 Oct 2013 12:38:47 GMT

Hi folks,

I have a pivot table.

Month	Stockvalue	ddMonths(MonatsLetzter,-1,1)	StockValue previous Month
Jan	56454	31.12.12 00:00:00	?
Feb	5454	31.01.13 00:00:00
Mar	454	28.02.13 00:00:00
Apr	787	31.03.13 00:00:00
...

Expression returns the last day of the previous Month from the current Line from date table

addMonths(MonatsLetzter,-1,1)

--> '31.01.13 00:00:00' works

Expression returns the stock value for the given date

Sum({$< Zeit_ID = {'31.01.13 00:00:00'} > } Lagerwert)

--> works

How can i replace the hard coded date with the first expression?

Thanks in advance
Greetings from Austria

Re: Need Support with Syntax with Set Analysis

salto — Thu, 31 Oct 2013 12:43:43 GMT

Hi,

does this work?

Sum({$< Zeit_ID = {addMonths(MonatsLetzter,-1,1) } > } Lagerwert)

Re: Need Support with Syntax with Set Analysis

Thu, 31 Oct 2013 12:50:54 GMT

Hi Salto,

thanks for anwsering - it doesn't work

Re: Need Support with Syntax with Set Analysis

salto — Thu, 31 Oct 2013 13:10:02 GMT

Could you upload a sample qvw so I can check? Many thanks!

Re: Need Support with Syntax with Set Analysis

Gysbert_Wassenaar — Thu, 31 Oct 2013 13:12:52 GMT

Set analysis calculates one set per chart, not per row. That means you can't use set analysis in this case. Perhaps you can use the above() function. But I think the approach you need is to create a table that links the month with the last day of the previous month. Can you post an example document? See this document for how to create such an example: Preparing examples for Upload - Reduction and Data Scrambling

Re: Re: Need Support with Syntax with Set Analysis

Thu, 31 Oct 2013 13:40:56 GMT

Wassenaar - thank you for posting.

Set analysis calculates one set per chart, not per row --> aha

But I think the approach you need is to create a table that links the month with the last day of the previous month.

--> yes, i will expand my date table with the "last day of the previous month" and with the "last day of the same month of the previous year"

My problem is that i am still thinking in the way of MS Analysis Services is working. I am also missing from AS the semi additive measures.

Greetings Jürgen

Re: Need Support with Syntax with Set Analysis

kaushalview — Thu, 31 Oct 2013 13:42:30 GMT

Hi,

try below expression

Sum({$< Zeit_ID = {"addMonths(MonatsLetzter,-1,1) "} > } Lagerwert)

Regards

kaushal Mehta

Re: Re: Need Support with Syntax with Set Analysis

Thu, 31 Oct 2013 13:57:27 GMT

But I think the approach you need is to create a table that links the month with the last day of the previous month.

--> This work - thanks

Can you give me a link of a summery where set analysis doesn't work?

Re: Need Support with Syntax with Set Analysis

Gysbert_Wassenaar — Thu, 31 Oct 2013 14:08:30 GMT

I don't think there is such a summary. Set analysis creates one set per chart, not per row. That's the important bit to remember. And it only works in chart expressions, not in the script. These two documents might be of interest: