topic Set analysis in QlikView

Set analysis

Anonymous — Tue, 21 Jun 2016 08:47:31 GMT

Hi Geniuses,

I want to create a chart like below

Client	Jan	Feb	Mar
Sony	5000	5020	5040
Samsung	6000	6030	6050

I have a data from the year 2010 to 2016, what client wants is he want to see the data starting from Jan 2016 so now what he wants is that all the data in the year 2010 to 2015 will combine together and will be shown in Jan and then Feb will be the cumulative sum of Jan and Feb

and Mar is cumulative sum of jan feb mar

the challenge is how to combine whole data of 2010 to 2015 irrespective of any month into the Jan 2016 month

when I say data it is the count of records.

Please help me in this

Thanks,

Re: Set analysis

sushil353 — Tue, 21 Jun 2016 08:52:34 GMT

Hi,

You need to create a month column ('Jan','Feb','Mar' etc) in your calendar table. and then use that Month column as dimension in your pivot table. and in expression you can simply use count(Fact).

For better resolution post some dummy data..

HTH

Sushil

Re: Set analysis

MK_QSL — Tue, 21 Jun 2016 09:09:29 GMT

I am considering that you have InvoiceDate in your data model.

Create a InvoiceMonthYear in your master calendar or in the same table where InvoiceDate is lying.

Date(MonthStart(TempDate),'MMM-YYYY') as InvoiceMonthYear

Now Create a Pivot Table

Dimension

1) Client

2) =If(InvoiceMonthYear >= YearStart(Today()), InvoiceMonthYear)

Tick Suppress When Value is Null

Expression

RangeSum(Before(IF(InvoiceMonthYear = Date(YearStart(Today()),'MMM-YYYY'), SUM(TOTAL <Customer> {<InvoiceMonthYear = {">=$(=Date(Min(InvoiceMonthYear),'MMM-YYYY'))<=$(=Date(YearStart(Today()),'MMM-YYYY'))"}>}Sales),

SUM({<InvoiceYear = {'$(=Year(Today()))'}>}Sales)), 0, ColumnNo()))

Let me know if this is now working.