Need Support with Syntax with Set Analysis - Qlik Community

Report Inappropriate Content · ‎2013-10-31

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

salto · ‎2013-10-31

Hi,

does this work?

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

Report Inappropriate Content · ‎2013-10-31

Hi Salto,

thanks for anwsering - it doesn't work

salto · ‎2013-10-31

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

Gysbert_Wassenaar · ‎2013-10-31

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

talk is cheap, supply exceeds demand

Report Inappropriate Content · ‎2013-10-31

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

kaushalview · ‎2013-10-31

Hi,

try below expression

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

Regards

kaushal Mehta

Report Inappropriate Content · ‎2013-10-31

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?

Gysbert_Wassenaar · ‎2013-10-31

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:

talk is cheap, supply exceeds demand