Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
New Contributor III

Set analysis

Hi Geniuses,

I want to create a chart like below

ClientJanFebMar
Sony500050205040
Samsung600060306050

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,

S

2 Replies
Highlighted
Honored Contributor II

Re: Set analysis

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

Highlighted
MVP
MVP

Re: Set analysis

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.